Insert XML in SQL Server

Question

Good afternoon all.

Currently, I have make a small demo with XML in SQl Server.

I have table with name: tb_xml_demo(ID, Name, Descr)

And each time when I insert to this table. ID column like this:

001, 002, 003 ....

This is my procedure

alter proc sp_xml_demo_cud
@p_xml xml
as
begin
    declare @dochandle int;

    exec sp_xml_preparedocument @dochandle output,@p_xml;

    insert into tb_xml_demo(id, name, descr)
        select  
            (select 
                 format(isnull(convert(int, max(id)), 0) + 1, '000') 
             from tb_xml_demo) id,
            name,
            descr
        from 
            OPENXML(@dochandle,'/root/item',2)
        with
            (name nvarchar(50),
             descr nvarchar(50),
             crud varchar(1)
            )
    end;

And this is my xml:

exec sp_xml_demo_cud
    '<root>
         <item>
            <name>9876543</name>
            <descr>1sdfsd</descr> 
         </item>
         <item>
            <name>333</name>
            <descr>333</descr> 
         </item>
     </root>';

And this is result after executing the procedure:

    id  Name     Descr  
    001 9876543  1sdfsd
    001 333      333

Please help me.

Thanks a lot.

The question is not clear at all - but I think his problem is that both his IDs are coming out "001". This is because of the convert(int, max(id)) + 1 bit... of course that will assign the same ID for every row in the batch, and even if you insert rows individually it's a truly terrible way of doing things. Just use an identity int and let the server generate your IDs! — beercohol
– beercohol, Commented Mar 25, 2016 at 11:28
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Mar 25, 2016 at 11:49

marc_s · Accepted Answer · 2016-03-25 12:04:21Z

3

I would recommend doing this:

create your table with a ID INT IDENTITY(1,1) column to let SQL Server handle the generation of unique ID values
add a computed column (I called it PaddedID) that uses the system-generated, valid ID to display with leading zeroes
parse the XML with the built-in, native XQuery functionality (instead of the legacy OPENXML stuff which is notorious for memory leaks)

This gives me this code:

-- create your table
CREATE TABLE tb_xml_demo
       (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        PaddedID AS RIGHT('0000' + CAST(ID AS VARCHAR(4)), 4) PERSISTED,
        Name VARCHAR(50), 
        Descr VARCHAR(100)
       )

-- declare your INPUT XML document
DECLARE @input XML = '<root>
         <item>
            <name>9876543</name>
            <descr>1sdfsd</descr> 
         </item>
         <item>
            <name>333</name>
            <descr>333</descr> 
         </item>
     </root>'

-- parse the XML using XQuery and insert the results into that newly created table
INSERT INTO dbo.tb_xml_demo
        (Name, Descr)
    SELECT
        ItemName = xc.value('(name)[1]', 'varchar(50)'),
        ItemDescr = xc.value('(descr)[1]', 'varchar(100)')
    FROM
        @input.nodes('/root/item') AS XT(XC)

-- select the values from the table    
SELECT * FROM dbo.tb_xml_demo

and this results in an output of:

answered Mar 25, 2016 at 12:04

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nguyen Van Dung Over a year ago

In different case, If ID column has complex structure like below : --Date: 2016/03/26 DO201603260001 DO201603260002 DO201603260003 DO201603260004 How can I generate this column data when I use insert command from XML. Please help me again. Thank you so much.

Collectives™ on Stack Overflow

Insert XML in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related