Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
11.1k views
in SQL Queries by 27 38 46
edited by

I have an XML column that holds XML property and attribute values and I want to know how can I select XML node value in SQL Server?

XML Query Example

select xml from XML

XML Query Result

sql select xml node value

XML Query Structure

<projects>
  <property name="id" value="1" />
  <property name="ProjectName" value="P1" />
  <property name="Status" value="1" />
</projects>

In the above XML Query Example, I need to select the xml attribute value fo the third node to show the status property with the below conditions

  • If XML node = 1 , show "In Progress"
  • f XML node = 2 , show "Late"
  • If XML node = 3 , show "Closed"

What's the best way to select XML node value attribute in SQL Server and get the below result

Project Name Status
P1 In Progress
P2 Late
P3 Closed

 


1 Answer

2 like 0 dislike
by 86 158 330
selected by
 
Best answer

How to Select XML Node Value in SQL Server?

To select values from XML field in SQL server, you should first understand the XML Query syntax as the following:

XML Query Syntax

Select CAST(XML Field Name as XML).value('(/Parent node Name/Chield Node Name/@ Attribute Name)[Chield Index]','Field Data Type') from table

XML Query Example

Let's match the above syntax with your XML Query

  • XML Field Name: XML
  • Parent node Name: projects
  • Chield Node Name: property
  • Attribute Name: value
  • Chield Index: chield index starts from 1, so in your case, for the second node it will be 2, and the third node will be 3.
  • Field Data Type: depends on the data types of the field value

So based on your XML Query Structure

<projects>
   <property name="id" value="1" />
   <property name="ProjectName" value="P1" />
   <property name="Status" value="1" />
</projects>

The XML query should be as the following

select CAST(XML as XML).value('(/projects/property/@value)[2]','varchar(500)') 'Project Name' from XML

To show the status node based on condition, you should use the CASE When statement

So, the final query should be

select CAST(XML as XML).value('(/projects/property/@value)[2]','varchar(500)') 'Project Name',
case 
    when CAST(XML as XML).value('(/projects/property/@value)[3]','int')=1 Then 'In Progress'
    when CAST(xml as XML).value('(/projects/property/@value)[3]','int')=2 Then 'Late'
    when CAST(xml as XML).value('(/projects/property/@value)[3]','int')=3 Then 'Closed'
end as 'Project Status'
from XML

Output

sql server xml query multiple nodes

by 27 38 46
0 0
Thanks for this clear explanation
If you don’t ask, the answer is always NO!
...