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.

Answer 1

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: