Extract XML Data from using TSQL

I have a table with this structure on a SQL Server 2008 DB:

Create Table Test (
	Id int,
	Attributes XML

Open in new window

and with this data:

1, '<Attributes><ProductAttribute ID="1"><ProductAttributeValue><Value>1</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="2"><ProductAttributeValue><Value>2</Value></ProductAttributeValue></ProductAttribute></Attributes>'
2, '<Attributes><ProductAttribute ID="1"><ProductAttributeValue><Value>1</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="2"><ProductAttributeValue><Value>3</Value></ProductAttributeValue></ProductAttribute></Attributes>'

Open in new window

For readability, here's what the first XML column data looks like:
	<ProductAttribute ID="1">
	<ProductAttribute ID="2">

Open in new window

I'm trying to extract the data form the table:

Id, Attribute

1, 1
1, 2
2, 1
2, 3      

I've tried following this post  to flatten the xml and extract the data I want but cannot get my head around it.  Is this the right path?
Who is Participating?
ste5anConnect With a Mentor Senior DeveloperCommented:
DECLARE @Test TABLE ( Id INT, Attributes XML );

VALUES  ( 1,
          N'<Attributes><ProductAttribute ID="1"><ProductAttributeValue><Value>1</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="2"><ProductAttributeValue><Value>2</Value></ProductAttributeValue></ProductAttribute></Attributes>' ),
        ( 2,
          N'<Attributes><ProductAttribute ID="3"><ProductAttributeValue><Value>1</Value></ProductAttributeValue></ProductAttribute><ProductAttribute ID="2"><ProductAttributeValue><Value>3</Value></ProductAttributeValue></ProductAttribute></Attributes>' );

        ProductAttributes.value('@ID', 'INT') AS ProductAttributeID ,
        ProductAttributeValues.value('.', 'INT') AS ProductAttributeValue
FROM    @Test T
        OUTER APPLY Attributes.nodes('/Attributes/ProductAttribute') AS A ( ProductAttributes )
        OUTER APPLY ProductAttributes.nodes('ProductAttributeValue/Value') AS B ( ProductAttributeValues );

Open in new window

canuckconsultingAuthor Commented:
Thank you!  I don't quite understand how the bits underlinedbelow work.  Can you explain how these select the data?

        ProductAttributes.value('@ID', 'INT') AS ProductAttributeID ,
        ProductAttributeValues.value('.', 'INT') AS ProductAttributeValue
Pawan KumarDatabase ExpertCommented:
You can read this from below URL. <<nopts..>>


Hope it helps !!
ste5anSenior DeveloperCommented:
Extracting (parsing) XML is based on the XPath language.

The first expression ProductAttributes.value('@ID', 'INT') extracts from the current node the attribute (the @ means read attribute values instead of elements) named ID. The current node is a ProductAttribute node. The result is converted to an integer.

The second expression ProductAttributeValues.value('.', 'INT') returns the text of the current node, where the current node is /Attributes/ProductAttribute/ProductAttributeValue/Value - the value node. The dot is just the syntax for getting the element content. Which is also converted to integer.
canuckconsultingAuthor Commented:
Thank you for your help and helpful explanation re xpath.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.