Post reply

Issue with nodes()

  • August 26, 2010 at 11:00 am

    #100737

    Hi to all

    I am working on a stored procedure that will receive an xml as paramenter.

    The xml could have the following structures.

    <requests AppId="">

    <pkg attribute1="" attribute2="" ..../>

    <pkg attribute1="" attribute2="" ..../>

    <pkg attribute1="" attribute2="" ..../>

    <pkg attribute1="" attribute2="" ..../>

    </requests>

    or

    could be just

    <pkg attribute1="" attribute2="" AppId="">

    I need a query that could be avialable to read both structures in the same query?

    I know that with openXml it is possible, but I don't know if it is possible with xquery.

    I need to implement this with xquery.

    thanks.

  • August 26, 2010 at 11:54 am

    #1212474

    This seems to be a good candidate for the exist() method.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 26, 2010 at 12:52 pm

    #1212499

    Here's an example of what I think Wayne is talking about:

    DECLARE @xml XML

    SET @xml=

    '<requests AppId="1">

    <pkg attribute1="a" attribute2="w"/>

    <pkg attribute1="b" attribute2="x"/>

    <pkg attribute1="c" attribute2="y"/>

    <pkg attribute1="d" attribute2="z"/>

    </requests>'

    /*-- or, alternatively

    SET @xml=

    '<pkg attribute1="a" attribute2="w" AppId="2"/>

    <pkg attribute1="b" attribute2="x" AppId="2"/>

    <pkg attribute1="c" attribute2="y" AppId="2"/>

    <pkg attribute1="d" attribute2="z" AppId="2"/>

    '

    */

    IF @xml.exist('/requests/@AppId') = 1

    SELECT

    c.value('@AppId[1]','varchar(10)') AS AppId,

    v.value('@attribute1[1]','varchar(10)') AS attribute1,

    v.value('@attribute2[1]','varchar(10)') AS attribute2

    FROM @xml.nodes('requests') T(c)

    CROSS APPLY

    T.c.nodes('pkg') U(v)

    ELSE

    SELECT

    c.value('@AppId[1]','varchar(10)') AS AppId,

    c.value('@attribute1[1]','varchar(10)') AS attribute1,

    c.value('@attribute2[1]','varchar(10)') AS attribute2

    FROM @xml.nodes('pkg') 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]

  • August 26, 2010 at 1:13 pm

    #1212511

    Hi thanks for your reply.

    is there another way? something like

    select ...

    from @xml.nodes('/requests/pkg or /pkg') t(b)

    something like that?

  • August 26, 2010 at 1:22 pm

    #1212522

    Not if you also need to know the value of the AppId attribute and not as long as your pkg nodes are not nested within a root node.

    If you don't need the AppId value and the nodes are wrapped inside a root node you could use:

    DECLARE @xml XML

    SET @xml=

    '<root><pkg attribute1="a" attribute2="w" AppId="2"/>

    <pkg attribute1="b" attribute2="x" AppId="2"/>

    <pkg attribute1="c" attribute2="y" AppId="2"/>

    <pkg attribute1="d" attribute2="z" AppId="2"/>

    </root>'

    SELECT

    c.value('@attribute1[1]','varchar(10)') AS attribute1,

    c.value('@attribute2[1]','varchar(10)') AS attribute2

    FROM @xml.nodes('/*/pkg') 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]

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

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