Insert a dynamic XML stored procedure

Question

I have an xml similar to this:

<main> <kim id="child1_id" name="this is child1" active="1" /> <kim id="Child2 id" name="this is child2" active="1" /> <lee id="child3_id" heigt="189" weight="70" /> </main> 

with kim and lee is the name of the tables in my database. Now I have to make a stored procedure to insert the value from xml above into kim and lee. I can get the table name using OPENXML, and I know that I can use such a query to insert (for example) the kim value:

 declare @tempChild1 table( id varchar(20), name varchar(50), active bit ) select C.value('@id', 'varchar(20)') as id, C.value('@name', 'varchar(50)') as name, C.value('@active', 'bit') as active, from @xml.nodes('/main/kim') t (C) insert into child1 select * from @tempChild1 

The problem is that this is dynamic xml, and I don't know which tables will come from xml, but I still have to make an xml-based insert request. Is there any way to do this in SQL? Can I make an expression as shown below from xml above?

 exec spx_kim @xml exec spx_lee @xml 

with @xml is the value of each kim and lee node in xml.

I really appreciate any help you give me.

Answer 1

In the end, I have to make a stored procedure manually for each table involved.

Answer 2

Try this option -

DDL:

 CREATE PROCEDURE dbo.usp_kim ( @XML XML ) AS BEGIN SET NOCOUNT ON; --INSERT INTO .... SELECT tcvalue('@id', 'VARCHAR(20)') , tcvalue('@name', 'VARCHAR(50)') , tcvalue('@active', 'BIT') FROM @XML.nodes('/main/kim') t(c) END GO CREATE PROCEDURE dbo.usp_lee ( @XML XML ) AS BEGIN --INSERT INTO .... SELECT tcvalue('@id', 'VARCHAR(20)') , tcvalue('@heigt', 'INT') , tcvalue('@weight', 'INT') FROM @XML.nodes('/main/lee') t(c) END GO 

Query:

 DECLARE @XML XML SELECT @XML = ' <main> <kim id="child1_id" name="this is child1" active="1" /> <kim id="Child2 id" name="this is child2" active="1" /> <lee id="child3_id" heigt="189" weight="70" /> </main>' EXEC dbo.usp_kim @XML = @XML EXEC dbo.usp_lee @XML = @XML