T-SQL Extracting Attribute Values from XML

Let's say you have a sample XML with a structure similar to below stored as a column in a database: 

<Batch>
<Presentations>
<Presentation>
<Slides>
<Slide ID='43'>
<SlideVariables>
<SlideVariable Name='v1' Value='1234'>
<SlideVariable Name='v2' Value='2234'>
<SlideVariable Name='v3' Value='3234'>
<SlideVariable Name='v4' Value='4234'>
</SlideVariables>
</Slide>
</Slides>
</Presentation>
</Presentations>
</Batch>

 The XML is stored in a column in the database table called, xmldata.

To extract attribute value at the [Value] of the SlideVarable, you would use a code similar to below:

select xmldata.value('data(/Batch/Presentations/Presentation/Slides/Slide[@ID="43"]/SlideVariables/SlideVariable/@Value)[1]','varchar(50)') v1,

xmldata.value('data(/Batch/Presentations/Presentation/Slides/Slide[@ID="43"]/SlideVariables/SlideVariable/@Value)[2]','varchar(50)') v2,

xmldata.value('data(/Batch/Presentations/Presentation/Slides/Slide[@ID="43"]/SlideVariables/SlideVariable/@Value)[3]','varchar(50)') v3,

xmldata.value('data(/Batch/Presentations/Presentation/Slides/Slide[@ID="43"]/SlideVariables/SlideVariable/@Value)[4]','varchar(50)') v4

FROM myDBTable

Would return '1234' as [v1], '2234' as [v2], etc…

Now, if you wish to extract the name of the attribute, in this case [Name], you would use a SQL similar to below:

xmldata.value('data(/Batch/Presentations/Presentation/Slides/Slide[@ID="43"]/SlideVariables/SlideVariable/@Name)[1]','varchar(50)')

 Would return 'v1' in this case…

Noticed that @Name[1] would get name from first element and @Name[2] would get that next element, etc..

Hope this helps you to Extracting Attribute Values from XML Using XQuery!

This sample code was based on an article found on: You can go there to find the original article

http://www.windows-tech.info/15/605e849d901cce90.php