question

Katie 1 avatar image
Katie 1 asked

Insert XML data into the database.

This is the data that i generated using the command SELECT * FROM abc.ccc.BR_MTR_IMPORT FOR XML AUTO

<abc.ccc.BR_MTR_IMPORT MTR_KEY="xxx-xxx-xxxx" TRAN_DATE="2010-06-06T12:00:00"     VERSION="2" FILE_ID="1660" ROW_STATE="2.75" PROCESS_STATE="1.34" LST_MODIFIED_DT="2010-06-11T16:40:18.553" LST_MODIFIED_USER="qwer" /> </br>

<abc.ccc.BR_MTR_IMPORT MTR_KEY="zzz-zzz-zzzz" TRAN_DATE="2010-06-06T12:00:00" VERSION="3" FILE_ID="1660" ROW_STATE="2.75" PROCESS_STATE="1.34" LST_MODIFIED_DT="2010-06-11T16:43:19.360" LST_MODIFIED_USER="qwer" /></br>

<abc.ccc.BR_MTR_IMPORT MTR_KEY="nnn-nnn-nnnn" TRAN_DATE="2010-06-06T12:00:00" VERSION="4" FILE_ID="1660" ROW_STATE="2.75" PROCESS_STATE="1.34" LST_MODIFIED_DT="2010-06-11T16:46:20.217" LST_MODIFIED_USER="qwer" /> </br>

<abc.ccc.BR_MTR_IMPORT MTR_KEY="hhh-hhh-hhhh" TRAN_DATE="2010-06-06T12:00:00" VERSION="5" FILE_ID="1660" ROW_STATE="2.75" PROCESS_STATE="1.34" LST_MODIFIED_DT="2010-06-11T16:52:20.973" LST_MODIFIED_USER="qwer" />

<abc.ccc.BR_MTR_IMPORT MTR_KEY="fff-fff-ffff" TRAN_DATE="2010-06-06T12:00:00" VERSION="1" FILE_ID="1660" ROW_STATE="2.75" PROCESS_STATE="1.34" LST_MODIFIED_DT="2010-06-11T16:37:17.450" LST_MODIFIED_USER="qwer" />

*/

presented above is the result i got from the database.

What i want to know is... is there a way that, if we do any change to the data in the xml document, and can it be inserted back into the database table? if so how.

To be more clear, if in this xml document if i change the file ID number to 1545.. can it be done through this XML and can i push it back through this XML document.

sql-server-2008xmlinsert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered

Here is one scenario. Suppose you modify the xml and then you want to update data in the underlying table based on changes in xml. Then you can treat xml as a normal rowset, meaning that you can join it with your underlying table based on the key column and update the table from that join. Here is a complete example:

-- the first part is simply a copy of your xml in your 
-- question declared as xml data type:

declare @xml xml;
set @xml = '
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="xxx-xxx-xxxx" 
    TRAN_DATE="2010-06-06T12:00:00"     
    VERSION="2" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:40:18.553" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="zzz-zzz-zzzz" 
    TRAN_DATE="2010-06-06T12:00:00" VERSION="3" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:43:19.360" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="nnn-nnn-nnnn" 
    TRAN_DATE="2010-06-06T12:00:00" 
    VERSION="4" 
    FILE_ID="1660" ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:46:20.217" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="hhh-hhh-hhhh" 
    TRAN_DATE="2010-06-06T12:00:00" 
    VERSION="5" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:52:20.973" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="fff-fff-ffff" 
    TRAN_DATE="2010-06-06T12:00:00" 
    VERSION="1" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:37:17.450" 
    LST_MODIFIED_USER="qwer" />';

-- now if you want to select data from above xml you can do this:

    select 
        item.value('@MTR_KEY', 'varchar(50)') MTR_KEY,
        item.value('@TRAN_DATE', 'datetime') TRAN_DATE,
        item.value('@VERSION', 'varchar(50)') VERSION,
        item.value('@FILE_ID', 'int') [FILE_ID],
        item.value('@ROW_STATE', 'decimal(10, 2)') ROW_STATE,
        item.value('@PROCESS_STATE', 'decimal(10, 2)') PROCESS_STATE,
        item.value('@LST_MODIFIED_DT', 'datetime') LST_MODIFIED_DT,
        item.value('@LST_MODIFIED_USER', 'varchar(50)') LST_MODIFIED_USER
        from @xml.nodes('abc.ccc.BR_MTR_IMPORT') r(item);

-- and if you want to update your table from the xml above, you can do this:

with records
(
    MTR_KEY, TRAN_DATE, VERSION, [FILE_ID], ROW_STATE, 
    PROCESS_STATE, LST_MODIFIED_DT, LST_MODIFIED_USER
) as
(
    select 
        item.value('@MTR_KEY', 'varchar(50)') MTR_KEY,
        item.value('@TRAN_DATE', 'datetime') TRAN_DATE,
        item.value('@VERSION', 'varchar(50)') VERSION,
        item.value('@FILE_ID', 'int') [FILE_ID],
        item.value('@ROW_STATE', 'decimal(10, 2)') ROW_STATE,
        item.value('@PROCESS_STATE', 'decimal(10, 2)') PROCESS_STATE,
        item.value('@LST_MODIFIED_DT', 'datetime') LST_MODIFIED_DT,
        item.value('@LST_MODIFIED_USER', 'varchar(50)') LST_MODIFIED_USER
        from @xml.nodes('abc.ccc.BR_MTR_IMPORT') r(item)
)
    update t
    set 
        TRAN_DATE = records.TRAN_DAT,
        VERSION = records.VERSION,
        [FILE_ID] = records.[FILE_ID],
        ROW_STATE = records.ROW_STATE,
        PROCESS_STATE = records.PROCESS_STATE,
        LST_MODIFIED_DT = records.LST_MODIFIED_DT,
        LST_MODIFIED_USER = records.LST_MODIFIED_USER
        from abc.ccc.BR_MTR_IMPORT t inner join records
            on t.MTR_KEY = records.MTR_KEY;

Hope this helps

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

I dont use XML much and I'd be paraphrasing Google if I did anything other than say to search for "insert xml into sql server" and there are loads of links, examples and How To's

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.