formatting result from tablename.query('data(//XMlnode)')

Question

Post reply

formatting result from tablename.query('data(//XMlnode)')

nams

Old Hand

Points: 316
More actions
March 10, 2009 at 11:19 am

#201226

Select columnname.query('data(//XMlnode)') from tablename
Gets all the values in a single line without any breaks.
Is there a way I can put a space or line break within the values generated by the above query?

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

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

Why not shred the text nodes into separate rows instead?

DECLARE @xml xml

SELECT @xml = '<Results>

<Group><XmlNode>A1<x>extra</x></XmlNode><XmlNode>B1</XmlNode></Group>

<Group><XmlNode>A2</XmlNode><XmlNode>B2</XmlNode></Group>

</Results>'

SELECT X.N.value('.', 'varchar(max)')

FROM @xml.nodes('//XmlNode//text()') AS X(N)

If you do need to concatenate the shredded rows back into a delimited string then you could do the following:

SELECT STUFF((SELECT ',' + X.N.value('.', 'varchar(max)')

FROM @xml.nodes('//XmlNode//text()') AS X(N) FOR XML PATH('')), 1, 1, '')

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

Try these

Select tablename.query('data(//XMlnode/text())')

Select tablename.query('data(//XMlnode//text())')

____________________________________________________

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

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

nams Old Hand Points: 316 More actions · Answer 3

HI,

Can you please tell me what is X and N in your code

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

Can you please tell me what is X and N in your code

They are aliases for the table and column that is generated by shredding the XML variable using the nodes XQuery method. They allow you to reference the derived table columns in the SELECT clause.