Shredding XML

Question

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

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

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

It's ok.... cracked it.

-- Query to return the item nos.

SELECT

convert(int,convert(varchar(50),I.query('text()'))) AS Item

FROM

@tableToHoldXML

CROSS APPLY xmlDataColumn.nodes('/list/item') AS Items(I)

Although if there is a more elegant solution i'd love to hear some suggestions.

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 2

Also this

SELECT

I.value('(./text())[1]','int') AS Item

FROM

@tableToHoldXML

CROSS APPLY xmlDataColumn.nodes('/list/item') AS Items(I)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 3

Thanks, that's more what i was after. 🙂

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne