Post reply

Parsing XML into a table

  • November 1, 2010 at 8:51 am

    #231066

    I need to run a sql statement to produce

    NAME VALUE

    addr1 20 Morris Ave

    addr2 NULL or ‘’

    addr3 NULL or ‘’

    city Wahroonga

    state New South Wales

    zip 2076

    from this...

    <?xml version="1.0" encoding="UTF-8"?>

    <xml>

    <target>

    <tag_pairs>

    <tag_pair><name>addr1</name><values><value>20 Morris Ave</value></values></tag_pair>

    <tag_pair><name>addr2</name><values><value></value></values></tag_pair>

    <tag_pair><name>addr3</name><values><value></value></values></tag_pair>

    <tag_pair><name>city</name><values><value>Wahroonga</value></values></tag_pair>

    <tag_pair><name>state</name><values><value>New South Wales</value></values></tag_pair>

    <tag_pair><name>zip</name><values><value>2076</value></values></tag_pair>

    </tag_pairs>

    </target>

    </xml>

    My elementary efforts using the .nodes() and corss apply have been fruitless.

    Thanks the help.

    Bill

  • November 1, 2010 at 9:06 am

    #1244873

    DECLARE @Data XML = '<?xml version="1.0" encoding="UTF-8"?>

    <xml>

    <target>

    <tag_pairs>

    <tag_pair>

    <name>addr1</name>

    <values>

    <value>20 Morris Ave</value>

    </values>

    </tag_pair>

    <tag_pair><name>addr2</name><values><value></value></values></tag_pair>

    <tag_pair><name>addr3</name><values><value></value></values></tag_pair>

    <tag_pair><name>city</name><values><value>Wahroonga</value></values></tag_pair>

    <tag_pair><name>state</name><values><value>New South Wales</value></values></tag_pair>

    <tag_pair><name>zip</name><values><value>2076</value></values></tag_pair>

    </tag_pairs>

    </target>

    </xml>'

    SELECT n.value('../name[1]', 'VARCHAR(MAX)') AS Name,

    n.value('value[1]', 'VARCHAR(MAX)') AS Value

    FROM @Data.nodes('/xml/target/tag_pairs/tag_pair/values') AS v(n)

    N 56°04'39.16"
    E 12°55'05.25"

  • November 1, 2010 at 9:23 am

    #1244882

    Thank you oh so much.

    Bill

  • November 1, 2010 at 9:46 am

    #1244894

    Here's a slightly different approach using CROSS APPLY.

    It usually is faster than the method Peso posted.

    But you should try both and decide by yourself 😀

    SELECT n.value('name[1]', 'VARCHAR(MAX)') AS Name,

    c.value('value[1]', 'VARCHAR(MAX)') AS Value

    FROM @Data.nodes('/xml/target/tag_pairs/tag_pair') AS v(n)

    CROSS APPLY

    v.n.nodes('values') as T(c)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 2, 2010 at 7:15 am

    #1245334

    Thank you, I 'filed' both methods.

    I appreciate the help.

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

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