Results 1 to 3 of 3

Thread: openxml

  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Unanswered: openxml

    How do I insert the following XML document in one row using open xml?


    <ROOT>
    <TransactionID>1000</TransactionID>
    <Plan type="xxxx"/>
    <Plan type="yyyy"/>
    </ROOT>

    I want the output as

    TID Type1 Type2
    1000 xxxx yyyy


    Thank You

  2. #2
    Join Date
    Jul 2003
    Location
    Stockholm, SWEDEN
    Posts
    3

    Wink Read this

    This tutorial should answer all your basic ques when dealing with xml and sql server...

    And if it doesnt, come back and ask your ques again.

    http://www.vbxml.com/tutorials/sqlxml/sqlxml.pdf

    -- Hobil Haidari
    -- VB SQL Developer, Administrator

  3. #3
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    ashy_16in,

    You can try the following:

    declare @Doc varchar(1000)
    select @Doc='
    <ROOT>
    <TransactionID>1000</TransactionID>
    <Plan type="xxxx"/>
    <Plan type="yyyy"/>
    </ROOT>'
    declare @did int
    exec sp_xml_preparedocument @did output, @doc
    SELECT *
    FROM OPENXML (@did, 'ROOT/Plan')
    WITH (
    TID int '../TransactionID',
    Type varchar(50) '@type')
    EXEC sp_xml_removedocument @did


    Please note, that the data will be returned in several rows - since it is unknown, how may elements named "Plan" you have.
    --
    kukuk

Posting Permissions

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