Post reply

Shredding XML using XQuery

  • January 19, 2012 at 1:29 pm

    #98311

    Hey all, I am having some issues with XML and XQuery in SQL. Here is basically what I want

    I have some XML like this

    <a>

    <b>one</b>

    <c>two</b>

    </a>

    <d>

    <b>three</b>

    <c>four</b>

    </d>

    .....

    And I want to write a SQL statement using XQuery that will return the following

    (column name) (column name)

    ----------------- ------------------

    one two

    three four

    Any thoughts?

  • January 19, 2012 at 1:46 pm

    #1436164

    Your XML is mal-formed. XQuery requires a single root element, which yours doesn't have and the end tag for your <C> element doesn't match. I've corrected the XML.

    DECLARE @x XML

    SET @x = '

    <root>

    <a>

    <b>one</b>

    <c>two</c>

    </a>

    <d>

    <b>three</b>

    <c>four</c>

    </d>

    </root>

    '

    You have two options. The first is to use the 'or' function.

    SELECT c.value('b[1]', 'varchar(10)'), c.value('c[1]', 'varchar(10)')

    FROM @x.nodes('/root/a, /root/d') AS T(c)

    The second option is to use a wildcard (and possibly filter in the where clause).

    SELECT c.value('b[1]', 'varchar(10)'), c.value('c[1]', 'varchar(10)')

    FROM @x.nodes('/root/*') AS T(c)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 20, 2012 at 7:13 am

    #1436471

    Thanks...I probably should have been more clear. the XML is a column in a table and I need to write this is a way it will work in a select statement with a bunch of other columns...basically, I need it set based, and not based on one XML value..if that makes sense?

  • January 20, 2012 at 8:48 am

    #1436561

    You just use a CROSS APPLY and replace the variable name with the column name.

    SELECT *

    FROM YourTable

    CROSS APPLY (

    SELECT c.value('b[1]', 'varchar(10)'), c.value('c[1]', 'varchar(10)')

    FROM YourColumn.nodes('/root/*') AS T(c)

    ) AS YourAlias(BValue, CValue)

    This is why providing representative sample data is so important. The closer the sample is to your actual situation, the more likely you will get a response tailored to that situation. Providing inadequate sample data results in responses that don't fully answer your question or possibly even responses that are totally off track.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 24, 2012 at 6:29 am

    #1437635

    Sorry for the delay, but that did exactly what I needed. Thanks for the help!

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

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