Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

XML Shred question

0 views
Skip to first unread message

RJ

unread,
Oct 10, 2009, 5:22:01 PM10/10/09
to
I am an XML rookie.

Given that, I have a table called Testdata with columns defined as
[ID] [int] IDENTITY(1,1) NOT NULL,
[DataAttributes] [xml] NULL

The rows of data in the DataAttributes columns looks like this:
<Benchmark>
<Attribute>1500658</Attribute>
<Attribute>22826.345750000000000</Attribute>
<Attribute>94</Attribute>
<Attribute>125</Attribute>
<Attribute>Index</Attribute>
</Benchmark>

I need a query that can return all the rows in the table with the xml broken
up into individual columns. Something like this but as columns not commas.
1, 1500658, 22826.345750000000000, 94, 125, Index
2, 12, 800.001, 101, 102, Pattern
….

Any help is greatly appreciated!


Bob

unread,
Oct 10, 2009, 7:32:01 PM10/10/09
to

Try this:

DECLARE @Testdata TABLE ( [ID] [int] IDENTITY NOT NULL, [DataAttributes]
[xml] )

INSERT @Testdata
SELECT '<Benchmark>


<Attribute>1500658</Attribute>
<Attribute>22826.345750000000000</Attribute>
<Attribute>94</Attribute>
<Attribute>125</Attribute>
<Attribute>Index</Attribute>

</Benchmark>'

SELECT
x.y.value('Attribute[1]', 'VARCHAR(MAX)'),
x.y.value('Attribute[2]', 'VARCHAR(MAX)'),
x.y.value('Attribute[3]', 'VARCHAR(MAX)'),
x.y.value('Attribute[4]', 'VARCHAR(MAX)'),
x.y.value('Attribute[5]', 'VARCHAR(MAX)')
FROM @Testdata t
CROSS APPLY [DataAttributes].nodes('Benchmark') x(y)

RJ

unread,
Oct 10, 2009, 9:46:01 PM10/10/09
to
Thank you Bob,

That got me going down the right right path!

0 new messages