Dynamic XML to Stored Procedure Insert

Question

I have xml similar like 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 tables in my database. Now I have to make a stored procedure to insert the value from the xml above to kim and lee. I can get the table name using OPENXML and I know that I can use query like this for inserting (for example) 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, it's a dynamic xml and I don't know what tables would come from the xml, but I still have to make an insert query based on the xml. Is there any possibilities to do it in SQL? Can I make the statement like below from the xml above?

exec spx_kim @xml
exec spx_lee @xml

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

I really appreciate every help you give me.

Answer 1

Try this one -

DDL:

CREATE PROCEDURE dbo.usp_kim
(
     @XML XML
)
AS BEGIN

     SET NOCOUNT ON;

     --INSERT INTO ....
     SELECT  
            t.c.value('@id', 'VARCHAR(20)')
          , t.c.value('@name', 'VARCHAR(50)')
          , t.c.value('@active', 'BIT')
     FROM @XML.nodes('/main/kim') t(c)


END
GO

CREATE PROCEDURE dbo.usp_lee
(
     @XML XML
)
AS BEGIN

     --INSERT INTO ....
     SELECT  
            t.c.value('@id', 'VARCHAR(20)')
          , t.c.value('@heigt', 'INT')
          , t.c.value('@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

Answer 2

After all, I have to make the stored procedure manually for each table involved