問題描述

我有以下格式的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>

我想將它們插入表中，因此在示例中該表應(yīng)該有 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

如何使用游標(biāo)或其他方式實現(xiàn)此目的?

How can I achive this using cursor or something else?

我嘗試過的.在實體的光標(biāo)中，我嘗試插入別名值，但只采用第一個別名.

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)

這篇關(guān)于將xml數(shù)據(jù)插入表存儲過程sql server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！