How to insert xml data into table in sql server 20

问题:

my table structure is

CREATE TABLE [dbo].[Emp](
    [ID] [int] NOT NULL,
    [EmpName] [varchar](50)  NOT NULL,
    [Sal] [int] NULL,
) 

in this emp table i want to insert data from a xml string

the xml is

<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp>
<ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>

suppose this xml is stored in a variable in my store procedure and i just want to insert this xml in such a way that in EMP table as a result ID data will insert into ID column, EmpName data will insert into EmpName column and Sal data will insert into Sal column.

so please tell me how to write the code in store procedure.

thanks

回答1:

Assuming XML sample as above:

<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Emp>
        <ID>3</ID>
        <EmpName>Dibyendu</EmpName>
        <Sal>3500</Sal>
    </Emp>
</Record>

Assuming the following table:

CREATE TABLE Employee
(
    [ID] [int] NOT NULL,  
    [EmpName] varchar(max) NOT NULL,   
    [Sal] [int] NULL
)

The following stored procedure, that uses xpaths, should do the trick

CREATE PROCEDURE AddEmployee
    @empXml xml
AS

INSERT INTO Employee
(
    ID,
    EmpName,
    Sal
)
VALUES
(
    @empXml.value('(/Record/Emp/ID)[1]', 'int'),
    @empXml.value('(/Record/Emp/EmpName)[1]', 'varchar(max)'),
    @empXml.value('(/Record/Emp/Sal)[1]', 'int')
)

Which you can then execute with:

exec AddEmployee '<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>' 

You will need to do a little refactoring if the Record XML could potentially include multiple 'Emp' elements.

回答2:

Have a look at something like

DECLARE @Xml XML

DECLARE @Emp TABLE(
        [ID] [int] NOT NULL, 
        [EmpName] varchar NOT NULL, 
        [Sal] [int] NULL
)

SELECT @Xml = '<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>'

SELECT  @Xml.value('(/Record/Emp/ID)[1]', 'int' ) ID,
        @Xml.value('(/Record/Emp/EmpName)[1]', 'VARCHAR(MAX)' ) EmpName,
        @Xml.value('(/Record/Emp/Sal)[1]', 'int' ) Sal

And if you were to have multiple rows you can try something like

DECLARE @Xml XML

SELECT @Xml = '<Record><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp><Emp><ID>4</ID><EmpName>TADA</EmpName><Sal>5</Sal></Emp></Record>'

SELECT  A.B.value('(ID)[1]', 'int' ) ID,
        A.B.value('(EmpName)[1]', 'VARCHAR(MAX)' ) EmpName,
        A.B.value('(Sal)[1]', 'int' ) Sal
FROM    @Xml.nodes('/Record/Emp') A(B)