print XML attribute value

Question

print XML attribute value

rash3554

SSCommitted

Points: 1984
More actions
January 24, 2017 at 3:29 am

#334409

I am trying to get value of an attribute from XML in SQL table
<?xml version="1.0" encoding="utf-8"?> <container> <Property Name="paramA" Vocabulary="someVocu"> <Property Name="paramB" Value="valueA" /> <Property Name="paramC" Value="valueB" /> </Property> <Property Name="paramA" Vocabulary="anotherVocu"> <Property Name="paramB" Value="valueY" /> <Property Name="paramC" Value="valueZ" /> </Property></container>
select x.XmlCol.value('(Property[@Name="paramB"]/@Value)[1]', 'varchar(50)') from tempTbl CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)
I am trying to print "valueA" and "valueY" I am getting a NULL.How can I do this?
Thank you
Rash

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182919 More actions · Answer 1

rash3554 - Tuesday, January 24, 2017 3:29 AM
I am trying to get value of an attribute from XML in SQL table
<?xml version="1.0" encoding="utf-8"?> <container> <Property Name="paramA" Vocabulary="someVocu"> <Property Name="paramB" Value="valueA" /> <Property Name="paramC" Value="valueB" /> </Property> <Property Name="paramA" Vocabulary="anotherVocu"> <Property Name="paramB" Value="valueY" /> <Property Name="paramC" Value="valueZ" /> </Property></container>
select x.XmlCol.value('(Property[@Name="paramB"]/@Value)[1]', 'varchar(50)') from tempTbl CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)
I am trying to print "valueA" and "valueY" I am getting a NULL.How can I do this?
Thank you
Rash

The property element is nested under another property element, adjust the path in the value function.
😎

<container> <Property Name="paramA" Vocabulary="someVocu"> <Property Name="paramB" Value="valueA" /> <Property Name="paramC" Value="valueB" /> </Property> <Property Name="paramA" Vocabulary="anotherVocu"> <Property Name="paramB" Value="valueY" /> <Property Name="paramC" Value="valueZ" /> </Property> </container>

This works:
select x.XmlCol.value('(Property/Property[@Name="paramB"]/@Value)[1]', 'varchar(50)') from tempTbl CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)

rash3554 SSCommitted Points: 1984 More actions · Answer 2

Thanks.
But this prints only valueA from <Property Name="paramB" Value="valueA" />

and not valueY from
<Property Name="paramB" Value="valueY" />
How do I iterate through all Property elements?

Thanks
Rash

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

rash3554 - Tuesday, January 24, 2017 4:11 AM
Thanks.
But this prints only valueA from <Property Name="paramB" Value="valueA" />
and not valueY from
<Property Name="paramB" Value="valueY" />
How do I iterate through all Property elements?
Thanks
Rash

Does this help?

declare @xml xml =
'<container>
<Property Name="paramA" Vocabulary="someVocu">
<Property Name="paramB" Value="valueA" />
<Property Name="paramC" Value="valueB" />
</Property>
<Property Name="paramA" Vocabulary="anotherVocu">
<Property Name="paramB" Value="valueY" />
<Property Name="paramC" Value="valueZ" />
</Property>
</container>'

select t.c.value('(@Value)[1]', 'varchar(10)')
from @xml.nodes('/container/Property/Property') t (c)
where t.c.value('(@Name)[1]','varchar(10)') = 'paramB'

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eirikur Eiriksson SSC Guru Points: 182919 More actions · Answer 4

rash3554 - Tuesday, January 24, 2017 4:11 AM
Thanks.
But this prints only valueA from <Property Name="paramB" Value="valueA" />
and not valueY from
<Property Name="paramB" Value="valueY" />
How do I iterate through all Property elements?
Thanks
Rash

Change the XPath to iterate through the Property nodes
😎

DECLARE @TTXML TABLE (TXML XML); INSERT INTO @TTXML(TXML) VALUES( '<?xml version="1.0" encoding="utf-8"?><container> <Property Name="paramA" Vocabulary="someVocu"> <Property Name="paramB" Value="valueA" /> <Property Name="paramC" Value="valueB" /> </Property> <Property Name="paramA" Vocabulary="anotherVocu"> <Property Name="paramB" Value="valueY" /> <Property Name="paramC" Value="valueZ" /> </Property> </container>');

SELECT PROP.DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE FROM @TTXML TX CROSS APPLY TX.TXML.nodes('/container/Property/Property[@Name="paramB"]') AS PROP(DATA);='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>

rash3554 SSCommitted Points: 1984 More actions · Answer 5

Eirikur Eiriksson - Tuesday, January 24, 2017 6:46 AM
rash3554 - Tuesday, January 24, 2017 4:11 AM
Thanks.
But this prints only valueA from <Property Name="paramB" Value="valueA" />
and not valueY from
<Property Name="paramB" Value="valueY" />
How do I iterate through all Property elements?
Thanks
Rash
Change the XPath to iterate through the Property nodes
😎
DECLARE @TTXML TABLE (TXML XML); INSERT INTO @TTXML(TXML) VALUES( '<?xml version="1.0" encoding="utf-8"?><container> <Property Name="paramA" Vocabulary="someVocu"> <Property Name="paramB" Value="valueA" /> <Property Name="paramC" Value="valueB" /> </Property> <Property Name="paramA" Vocabulary="anotherVocu"> <Property Name="paramB" Value="valueY" /> <Property Name="paramC" Value="valueZ" /> </Property> </container>');
SELECT PROP.DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE FROM @TTXML TX CROSS APPLY TX.TXML.nodes('/container/Property/Property[@Name="paramB"]') AS PROP(DATA);='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>='font-size:9.0pt;mso-bidi-font-size:9.5pt;line-height:107%;font-family:"courier>

Thanks so much. I am not very comfortable with XML parsing in SQL server. Can you suggest any tutorials

Eirikur Eiriksson SSC Guru Points: 182919 More actions · Answer 6

You are very welcome
😎

I did post few resources few weeks back on this thread

Eirikur Eiriksson SSC Guru Points: 182919 More actions · Answer 7

Just a quick thought, if you don't know the actual structure of the XML but you do know the name of the node element and a property value of that element then you can use this traversing syntax to find those elements
😎

SELECT PROP.DATA.value('@Name' ,'VARCHAR(50)') AS PARAM_NAME ,PROP.DATA.value('@Value','VARCHAR(50)') AS PARAM_VALUE FROM @TTXML TX CROSS APPLY TX.TXML.nodes('//Property[@Name="paramB"]') AS PROP(DATA);