Link to home
Start Free TrialLog in
Avatar of canuckconsulting
canuckconsultingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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:
<Attributes>
	<ProductAttribute ID="1">
		<ProductAttributeValue>
			<Value>1</Value>
		</ProductAttributeValue>
	</ProductAttribute>
	<ProductAttribute ID="2">
		<ProductAttributeValue>
			<Value>2</Value>
		</ProductAttributeValue>
	</ProductAttribute>
</Attributes>

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?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of canuckconsulting

ASKER

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
You can read this from below URL. <<nopts..>>

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.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.
Thank you for your help and helpful explanation re xpath.