Post reply

Query XML

  • February 8, 2021 at 12:11 pm

    Go to Answer

    #3842432

    I have not used XML for some time but now need to query against a xml format I have not used before. The best I seem to be able to get is blank columns:

    DECLARE @xml xml = 
    '<Users>
    <User id="user1">
    <add key="name" value="user1name" />
    <add key="dept" value="user1Dept" />
    <add key="email" value="user1@somewhere.com" />
    <add key="tel" value="1234" />
    </User>
    <User id="user2">
    <add key="name" value="user2name" />
    <add key="dept" value="user2Dept" />
    <add key="email" value="user2@somewhere.com" />
    </User>
    <User id="user3">
    <add key="name" value="user3name" />
    <add key="dept" value="user3Dept" />
    <add key="email" value="user3@somewhere.com" />
    <add key="mobile" value="5678" />
    </User>
    </Users>';

    SELECT N.usernode.query('id').value('.', 'varchar(10)') AS id
    ,N.usernode.query('email').value('.', 'varchar(10)') AS email
    FROM (values (@xml)) X(xmlcol)
    CROSS APPLY X.xmlcol.nodes(N'Users/User') AS N (usernode);

    Any ideas?

  • February 8, 2021 at 1:12 pm

    Answer

    #3842448

    See if this works

     

    SELECT N.usernode.value('@id','varchar(10)') AS id
    ,N.usernode.value('(add[@key="email"])[1]/@value', 'varchar(30)') AS email
    FROM (values (@xml)) X(xmlcol)
    CROSS APPLY X.xmlcol.nodes(N'/Users/User') AS N (usernode);

    ____________________________________________________

    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

  • February 8, 2021 at 1:19 pm

    #3842450

    Thank you Mark. I was getting very confused.

Viewing 3 posts - 1 through 2 (of 2 total)

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