Nested XML query

Question

Post reply

Nested XML query

v.sadangi

Newbie

Points: 5
More actions
June 13, 2011 at 11:52 am

#389721

I need to read the <Maintenance> node value as XML data. How we can read that ?
DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>3 year warrenty</Warranty>
<Maintenance><Data>3 year parts and labor extended maintenance is available</Data></Maintenance>
</Features>
</ProductDescription>
</Root>'
Select @myDoc.value('(/Root/ProductDescription/Features/Warranty/text())[1]', 'varchar(max)')

Viewing 3 posts - 1 through 2 (of 2 total)

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

!2Brite SSC Veteran Points: 219 More actions · Answer 1

not really sure i understand what you are asking....

This?

Select @myDoc.query('/Root/ProductDescription/Features/Maintenance/*')

paul_ramster Ten Centuries Points: 1382 More actions · Answer 2

Here are some other options, all of which return different answer, yet all satisfy your query. It would be much better if you could explain what you're looking for a little more clearly.

DECLARE @myDoc xml

DECLARE @ProdID int

SET @myDoc = '<Root>

<ProductDescription ProductID="1" ProductName="Road Bike">

<Features>

<Warranty>3 year warrenty</Warranty>

<Maintenance><Data>3 year parts and labor extended maintenance is available</Data></Maintenance>

</Features>

</ProductDescription>

<ProductDescription ProductID="2" ProductName="Dirt Bike">

<Features>

<Warranty>1 year warrenty</Warranty>

<Maintenance><Data>2 year parts and labor extended maintenance is available</Data><x>other</x></Maintenance>

</Features>

</ProductDescription>

</Root>'

Select @myDoc.query('/Root/ProductDescription/Features/Maintenance/*') returns 1 row containing an XML fragment containing elements within Maintenance tags from any product.

Select doc.query('.')

from @myDoc.nodes('/Root/ProductDescription/Features/Maintenance/*') q(doc) returns 3 rows, each one containing a separate XML fragment.

Select doc.query('.')

from @myDoc.nodes('/Root/ProductDescription/Features/Maintenance') q(doc) returns 2 rows, one for each Product, each of which contains all the elements within the Maintenance element.