Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    2

    Unanswered: read xml node elements using sql query

    Hi All,

    Iam having the example xml with the data as shown below,

    declare @x xml
    set @x = '<SinterklaasWishlists>
    <child>
    <name>Tim</name>
    <wishlist>
    <article>
    <artno>21491269</artno>
    <description>Crane</description>
    <price>12.50</price>
    </article>
    <article>
    <artno>21499517</artno>
    <description>Keyboard</description>
    <price>10</price>
    </article>
    <article>
    <artno>21521591</artno>
    <description>Crime Investigation Game</description>
    <price>9.95</price>
    </article>
    </wishlist>
    </child>
    <child>
    <name>Tim2</name>
    <wishlist>
    <article>
    <artno>3145678</artno>
    <description>Mouse</description>
    <price>12.50</price>
    </article>
    </wishlist>
    </child>
    </SinterklaasWishlists>'



    I want to extract the elements of xml using sql query and insert the data into the table as shown below:

    CHILDNAME ARTICLE_NUMBER DESCRIPTION PRICE
    --------------- -------------------- ----------------------------------- -----------
    Tim1 21491269 Crane 12.50
    Tim1 21499517 Keyboard 10
    Tim1 21521591 Crime Investigation Game 9.95
    Tim2 3145678 Mouse 12.50

    Kindly help me on the same

    Thanks,
    Ram

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: read xml node elements using sql query

    The following SQL is a possible solution for you:

    Code:
    select
      a.value('(../../name)[1]','varchar(128)') as [CHILDNAME]
     ,a.value('artno[1]','varchar(32)') as [ARTICLE_NUMBER]
     ,a.value('description[1]','varchar(1024)') as [DESCRIPTION]
     ,a.value('price[1]','money') as [PRICE]
    from @x.nodes('//article') X(A)
    To discover more about XQuery and XPath, click here.

  3. #3
    Join Date
    Dec 2011
    Posts
    2
    Thanks dude..i got it worked

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •