问题描述
我有以下格式的xml
<Entity> <name>John</name> <aliases><alias>Johnny</alias></aliases> <aliases><alias>Johnson</alias></aliases> </Entity> <Entity> <name>Smith</name> <aliases><alias>Smithy</alias></aliases> <aliases><alias>Schmit</alias></aliases> </Entity>
我想将它们插入表中,因此在示例中该表应该有 4 条记录.
I want to insert them in table so the table should have 4 records in the example.
列是名称和别名.
name | alias John | Johnny John | Johnson Smith| Smithy Smith| Schmit
如何使用游标或其他方式实现此目的?
How can I achive this using cursor or something else?
我尝试过的.在实体的光标中,我尝试插入别名值,但只采用第一个别名.
What i have tried. In cursor for entity i try insert alias value,but only first alias is taken.
insert into TESTTABLE (EntityID,Alias) select @EntityID as EntityID, Alias from OpenXml(@ixml, '/Aliases',2) with ( Alias varchar(255) '.' )
推荐答案
DECLARE @XML AS XML= N' <Entity> <name>John</name> <aliases><alias>Johnny</alias></aliases> <aliases><alias>Johnson</alias></aliases> </Entity> <Entity> <name>Smith</name> <aliases><alias>Smithy</alias></aliases> <aliases><alias>Schmit</alias></aliases> </Entity>' INSERT INTO @tblTest(firstName,LastName) SELECT t1.c.value('../name[1]','varchar(100)') As FirstName,t1.c.value('alias[1]','varchar(50)') as SecondName FROM @xml.nodes('/Entity/aliases') t1(c)