canuckconsulting
asked on
Extract XML Data from using TSQL
I have a table with this structure on a SQL Server 2008 DB:
and with this data:
For readability, here's what the first XML column data looks like:
I'm trying to extract the data form the table:
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?
Create Table Test (
Id int,
Attributes XML
)
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>'
For readability, here's what the first XML column data looks like:
<Attributes>
<ProductAttribute ID="1">
<ProductAttributeValue>
<Value>1</Value>
</ProductAttributeValue>
</ProductAttribute>
<ProductAttribute ID="2">
<ProductAttributeValue>
<Value>2</Value>
</ProductAttributeValue>
</ProductAttribute>
</Attributes>
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can read this from below URL. <<nopts..>>
https://msdn.microsoft.com/en-IN/library/ms188282.aspx
Hope it helps !!
https://msdn.microsoft.com/en-IN/library/ms188282.aspx
Hope it helps !!
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.val ue('.', 'INT') returns the text of the current node, where the current node is /Attributes/ProductAttribu te/Product AttributeV alue/Value - the value node. The dot is just the syntax for getting the element content. Which is also converted to integer.
The first expression ProductAttributes.value('@
The second expression ProductAttributeValues.val
ASKER
Thank you for your help and helpful explanation re xpath.
ASKER
ProductAttributes.value('@ID', 'INT') AS ProductAttributeID ,
ProductAttributeValues.val