Extract XML value from SQL column

Question

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!

Thanks Robin. It was my first post. I just had to work out how to display the xml as code. :) — Kaspanitz, Jun 26 '16 at 07:19
Maybe check this out http://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column which is specific to SQL Server. — Robin Mackenzie, Jun 26 '16 at 07:22

gofr1 · Accepted Answer · 2016-06-26T09:20:15.573

2

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

edited Jun 26 '16 at 09:20

answered Jun 26 '16 at 07:22

gofr1

15,741
11
42
52

Thanks for the response. I get the following error when I try to do that: Cannot find either column "AlertParams" or the user-defined function or aggregate "AlertParams.value", or the name is ambiguous. AlertParams is the column name in my table. The query I used was: SELECT [AlertParams].value('(/AlertParameters/AlertParameter3)[1]','nvarchar(max)') from [dbo].[AlertView]. I think yours work because you cast the string to XML whereas my column might not be XML? – Kaspanitz Jun 26 '16 at 07:30
Then show the query that you use, please. My example is 100% working for the sample you provided. The column is `xml` datatype or `nvarchar`? What is the name of the column? That error says that there is no column named `AlertParams`... – gofr1 Jun 26 '16 at 07:32
The column is nvarchar(max). – Kaspanitz Jun 26 '16 at 07:35
So that is a problem. You need to cast it as xml and then use XQuery value. – gofr1 Jun 26 '16 at 07:44
The problem with using the value() function is that the column is not in XML format it is nvarchar(max). I've verified by changing gofr1's sample to DECLARE @test TABLE ( String nvarchar(max) ) – Kaspanitz Jun 26 '16 at 07:45
Ok, how do I cast it as part of a select statement to XML? Sorry, I am quite new at SQL queries. – Kaspanitz Jun 26 '16 at 07:45

Extract XML value from SQL column

1 Answers1