动态XML到存储过程插入

I have xml similar like this:

我有类似这样的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>

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:

kim和lee是我数据库中表的名称。现在我必须创建一个存储过程来将上面的xml中的值插入到kim和lee中。我可以使用OPENXML获取表名，我知道我可以使用这样的查询来插入（例如）kim值：

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?

问题是，它是一个动态的xml，我不知道哪些表会来自xml，但我仍然需要根据xml进行插入查询。有没有可能在SQL中做到这一点？我可以从上面的xml中做出如下所示的声明吗？

exec spx_kim @xml
exec spx_lee @xml

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

with @xml是xml中每个kim和lee节点的值。

I really appreciate every help you give me.

我非常感谢你给我的每一个帮助。

2 个解决方案

#1

Try this one -

试试这个 -

DDL:

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

#2

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

毕竟，我必须为每个涉及的表手动制作存储过程

#1