Post reply

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

  • 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?

  • March 10, 2009 at 2:10 pm

    #957073

    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, '')

  • March 10, 2009 at 2:20 pm

    #957079

    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

  • March 19, 2009 at 1:37 pm

    #963074

    HI,

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

  • March 20, 2009 at 3:25 am

    #963382

    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.

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

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