CODEWITHSUNDEEP
sqlsql-server
Kaspanitz
Kaspanitz

Reputation: 13

Extract XML value from SQL column

I have a column with content that resembles XML.

An example of the XML is below:

<AlertParameters>
    <AlertParameter1>Database drive C: is below critical threshold on space for last 00:15:00.
Note: Data may be stale. To get current data, run: Get-ServerHealth -Identity 'Serverxx' -HealthSet 'MailboxSpace'
Note: Subsequent detected alerts are suppressed until the health set is healthy again.
    </AlertParameter1>
    <AlertParameter2>http://technet.microsoft.com/en-us/library/ms.exch.scom.MailboxSpace(EXCHG.150).aspx?v=15.0.847.32
    </AlertParameter2>
    <AlertParameter3>MailboxSpace health set unhealthy (StorageLogicalDriveSpaceMonitor/C:) - Exchange Server Alert: Database drive C: is below critical threshold on space for last 00:15:00.
    </AlertParameter3>
</AlertParameters>

I need to extract the value between the AlertParameter3 tags. I've tried the value function, but cannot get it to work. I get an error i.e:

Cannot find either column "AlertParams" or the user-defined function or aggregate "AlertParams.Value", or the name is ambiguous.

All the examples I see involve either specifying the XML namespace or declaring the XML text. How can I extract this data from the column if I need to do a select on the column and do not have a namespace?

Any assistance will be much appreciated!

Upvotes: 0

Views: 9033

Answers (1)

gofr1
gofr1

Reputation: 15977

You can extract what you need using xQuery.

DECLARE @test TABLE (
    String xml
)

INSERT INTO @test VALUES 
('<AlertParameters>
<AlertParameter1>Database drive C: is below critical threshold on space for last 00:15:00.
Note: Data may be stale. To get current data, run: Get-ServerHealth -Identity ''Serverxx'' -HealthSet ''MailboxSpace''
Note: Subsequent detected alerts are suppressed until the health set is healthy again.
</AlertParameter1>
<AlertParameter2>http://technet.microsoft.com/en-us/library/ms.exch.scom.MailboxSpace(EXCHG.150).aspx?v=15.0.847.32
</AlertParameter2>
<AlertParameter3>MailboxSpace health set unhealthy (StorageLogicalDriveSpaceMonitor/C:) - Exchange Server Alert: Database drive C: is below critical threshold on space for last 00:15:00.
</AlertParameter3>
</AlertParameters>')


SELECT String.value('(/AlertParameters/AlertParameter3)[1]','nvarchar(max)')
FROM @test

Output:

MailboxSpace health set unhealthy (StorageLogicalDriveSpaceMonitor/C:) - Exchange Server Alert: Database drive C: is below critical threshold on space for last 00:15:00.

If column has nvarchar datatype then use CAST:

SELECT CAST(String as xml).value('(/AlertParameters/AlertParameter3)[1]','nvarchar(max)')
FROM @test

Upvotes: 2

Related Questions