SQL select xml node value

Question

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

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

 

Answer 1

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

Comments

Thanks for this clear explanation