Shredding XML using XQuery

Question

Shredding XML using XQuery

SQL STEWIE

SSCommitted

Points: 1837
More actions
January 19, 2012 at 1:29 pm

#98311

Hey all, I am having some issues with XML and XQuery in SQL. Here is basically what I want
I have some XML like this
<a>
one
<c>two
</a>
<d>
three
<c>four
</d>
.....
And I want to write a SQL statement using XQuery that will return the following
(column name) (column name)
----------------- ------------------
one two
three four
Any thoughts?

Viewing 5 posts - 1 through 4 (of 4 total)

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

drew.allen SSC Guru Points: 76989 More actions · Answer 1

Your XML is mal-formed. XQuery requires a single root element, which yours doesn't have and the end tag for your <C> element doesn't match. I've corrected the XML.

DECLARE @x XML

SET @x = '

<root>

<a>

one

<c>two</c>

</a>

<d>

three

<c>four</c>

</d>

</root>

'

You have two options. The first is to use the 'or' function.

SELECT c.value('b[1]', 'varchar(10)'), c.value('c[1]', 'varchar(10)')

FROM @x.nodes('/root/a, /root/d') AS T(c)

The second option is to use a wildcard (and possibly filter in the where clause).

SELECT c.value('b[1]', 'varchar(10)'), c.value('c[1]', 'varchar(10)')

FROM @x.nodes('/root/*') AS T(c)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQL STEWIE SSCommitted Points: 1837 More actions · Answer 2

Thanks...I probably should have been more clear. the XML is a column in a table and I need to write this is a way it will work in a select statement with a bunch of other columns...basically, I need it set based, and not based on one XML value..if that makes sense?

drew.allen SSC Guru Points: 76989 More actions · Answer 3

You just use a CROSS APPLY and replace the variable name with the column name.

SELECT *

FROM YourTable

CROSS APPLY (

SELECT c.value('b[1]', 'varchar(10)'), c.value('c[1]', 'varchar(10)')

FROM YourColumn.nodes('/root/*') AS T(c)

) AS YourAlias(BValue, CValue)

This is why providing representative sample data is so important. The closer the sample is to your actual situation, the more likely you will get a response tailored to that situation. Providing inadequate sample data results in responses that don't fully answer your question or possibly even responses that are totally off track.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQL STEWIE SSCommitted Points: 1837 More actions · Answer 4

Sorry for the delay, but that did exactly what I needed. Thanks for the help!