Post reply

insert XML data to SQL server two tables

  • March 30, 2009 at 5:52 am

    #73595

    hi,

    there is a XML data like this

    -

    -

    -

    -

    there is two piece on that xml table first piece is INVOISE and second piece is TRANSACTIONS

    and two sql tables :

    1 INVOICE

    2 TRANSACTIONS

    they are inner joined by primary key NUMBER

    is there a QUERY or a tool for insert this XML data to all these two tables with only one action??

    We are inserting these datas by vb.net, we take pieces by .net from xml and insert first table then insert second table. But if an error occure on second transaction on second table, we can't roll back the first transaction on first table

    i hope i could tell my problem exactly

  • March 30, 2009 at 6:19 am

    #968247

    sory,

    the xml data do not seem, i send it with attachement

  • March 30, 2009 at 9:30 am

    #968457

    proposal deleted due to nonworking solution under the given environment (XQuery together XML data type proposed, which doesn't work on SS2K).

    Revised proposal see post

    http://www.sqlservercentral.com/Forums/FindPost686570.aspx


    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]

  • March 30, 2009 at 10:09 am

    #968487

    Lutz: XML datatype is SQL Server 2005. You'll need to use OPENXML instead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 30, 2009 at 3:17 pm

    #968722

    Hi yaman bas,

    first of all I need to apologize for recommending a solution that doesn't work on your platform...

    I didn't pay enough attention to what forum you posted in...:blush:

    But, [thank you, Barry! ;-)], my mistake has been detected, giving me the chance to correct it.

    The following link might also be helpful:

    http://www.sqlservercentral.com/articles/Advanced+Querying/queryingxmlfilesusingsql2000/822/

    Here's my revised version:

    create proc [dbo].[importxml] @doc ntext

    as

    DECLARE @idoc int

    --Create an internal representation of the XML document.

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    -- Execute a SELECT statement that uses the OPENXML rowset provider.

    SELECT *

    FROM OPENXML (@idoc, '/SALES_INVOICES/INVOICE',2)

    WITH (TYPE int 'TYPE',

    NUMBER varchar(10) 'NUMBER',

    DATE varchar(18) 'DATE')

    SELECT *

    FROM OPENXML (@idoc, '/SALES_INVOICES/INVOICE/TRANSACTIONS/TRANSACTION',2)

    WITH (TYPE int '../../TYPE',

    NUMBER varchar(10) '../../NUMBER',

    MASTER_CODE varchar(18) 'MASTER_CODE',

    QUANTITY varchar(18) 'QUANTITY',

    UNIT_CODE varchar(18) 'UNIT_CODE')

    EXEC sp_xml_removedocument @idoc


    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 4 (of 4 total)

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