parse XML using sql

  • <?xml version="1.0" encoding="utf-8"?>

    <a>

    <b xmlns="b">

    <c>germany</c>

    </b>

    </a>

    How can you extract the word Germany using sql?

  • I tried this but no success

    WITH XMLNAMESPACES(DEFAULT 'b')

    select

    tab.col.value('c[1]', 'NVARCHAR(100)') AS Country1

    from

    @xml.nodes('/a/b')tab(col)

  • I pretty much suck when it comes to handling XML. 😀

    Does anyone have an answer for this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, you gotta learn some XML !

    p.gauci, declare the namespace explicitly and then use it to reference the nodes b and c....

    ;with xmlnamespaces( 'b' as ns_b )

    select nd.value('(./text())[1]','varchar(20)')

    from @xml.nodes('/a/ns_b:b/ns_b:c') x(nd)

    Note that I gave the namespace an alias of ns_b to differentiate it from the node called "b".

    Also, note that I used ./text() to extract the value - it's slightly quicker than just "."

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (2/13/2011)


    Jeff, you gotta learn some XML !

    Ok... how about an article on it? 😀

    In the meantime, care to explain the code you wrote so an XML dummy like me can understand it? Also, is there a good book or link on the subject because, like you said, I've gotta learn it and I figure you could point me in the right direction.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, I don't think another article is needed - Jacob Sebastian has already written a fantastic series of articles available here on SSC called XML Workshop - they range from the basics http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/ to more advanced techniques - ( he has done 26 of them now http://www.sqlservercentral.com/articles/XQUERY/70548/ )

    I will certainly try to explain the code when I get more time - later today perhaps.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (2/14/2011)


    Jeff, I don't think another article is needed - Jacob Sebastian has already written a fantastic series of articles available here on SSC called XML Workshop - they range from the basics http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/ to more advanced techniques - ( he has done 26 of them now http://www.sqlservercentral.com/articles/XQUERY/70548/ )

    I will certainly try to explain the code when I get more time - later today perhaps.

    If Jacob's articles are as good as you say, no need for you to break down your code. Thanks for the links... I'll study them tonight. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • http://technet.microsoft.com/en-us/library/ms345122(SQL.90).aspx is a good white paper on XQuery that you may want to look at.

    At the bottom of this white paper are links to three other decent xml white papers on XML data type, xml indexing and best practices in sql server 2005. Unfortunately, the links appear to be broken, so you might have to google the titles to get to those articles.

  • nadabadan (2/14/2011)


    http://technet.microsoft.com/en-us/library/ms345122(SQL.90).aspx is a good white paper on XQuery that you may want to look at.

    At the bottom of this white paper are links to three other decent xml white papers on XML data type, xml indexing and best practices in sql server 2005. Unfortunately, the links appear to be broken, so you might have to google the titles to get to those articles.

    Thank you for the additional information. I'll check it out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks mister.magoo

  • I was in the same boat as you regarding XML when I started using Policy Based Management and XEvents...

    Bought the following book and studied it in conjunction with googling.

    APress Pro SQL Server 2008 XML Written by Michael Coles.

  • Thanks for the book reference. I'll take a look. Michael Coles is one of the "good guys".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Viewing 12 posts - 1 through 11 (of 11 total)

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