SQL Server 将 XML 子节点附加到父节点

2021-11-16 00:00:00 xml append sql-server xmlnode xml.modify

我需要一个脚本来将新的 xml 子节点插入/附加到预先存在的 xml 父节点.

I need to have a script which can insert / append new xml child nodes to a pre-existing xml parent node.

--New child nodes
DECLARE @XMLChildData XML
SET @XMLChildData = '
<Persons>
    <Person>
        <Firstname>Gary</Firstname>
        <Surname>Smith</Surname>
        <Telephone>0115547899</Telephone>
        <Address>
            <AddressLine>1 Church Lane</AddressLine>
            <AddressLine>Rosebank</AddressLine>
            <AddressLine>Houghton</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Wayne</Firstname>
        <Surname>Farmey</Surname>
        <Telephone>0117453269</Telephone>
        <Address>
            <AddressLine>51 Oak Street</AddressLine>
            <AddressLine>Rivionia</AddressLine>
            <AddressLine>Sandton</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Mark</Firstname>
        <Surname>Jones</Surname>
        <Telephone>0119854741</Telephone>
        <Address>
            <AddressLine>4 Arum Lane</AddressLine>
            <AddressLine>Glen Hazel</AddressLine>
            <AddressLine>Johannesburg</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
</Persons>'

--Existing parent node
DECLARE @XMLParentData XML
SET @XMLParentData = '
<Persons>
    <Person>
        <Firstname>Sarah</Firstname>
        <Surname>Gray</Surname>
        <Telephone>0113265874</Telephone>
        <Address>
            <AddressLine>78 Emerl Aveune</AddressLine>
            <AddressLine>Fourways</AddressLine>
            <AddressLine>Sandton</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Jenna</Firstname>
        <Surname>Reed</Surname>
        <Telephone>0114781102</Telephone>
        <Address>
            <AddressLine>6 Park Lane</AddressLine>
            <AddressLine>Parkhurst</AddressLine>
            <AddressLine>Rosebank</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Mike</Firstname>
        <Surname>Wilke</Surname>
        <Telephone>0116532003</Telephone>
        <Address>
            <AddressLine>22 High Road</AddressLine>
            <AddressLine>Modderfontein</AddressLine>
            <AddressLine>Edenvale</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
</Persons>'

我希望最终结果是:

<Persons>
    <Person>
        <Firstname>Sarah</Firstname>
        <Surname>Gray</Surname>
        <Telephone>0113265874</Telephone>
        <Address>
            <AddressLine>78 Emerl Aveune</AddressLine>
            <AddressLine>Fourways</AddressLine>
            <AddressLine>Sandton</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Jenna</Firstname>
        <Surname>Reed</Surname>
        <Telephone>0114781102</Telephone>
        <Address>
            <AddressLine>6 Park Lane</AddressLine>
            <AddressLine>Parkhurst</AddressLine>
            <AddressLine>Rosebank</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Mike</Firstname>
        <Surname>Wilke</Surname>
        <Telephone>0116532003</Telephone>
        <Address>
            <AddressLine>22 High Road</AddressLine>
            <AddressLine>Modderfontein</AddressLine>
            <AddressLine>Edenvale</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Gary</Firstname>
        <Surname>Smith</Surname>
        <Telephone>0115547899</Telephone>
        <Address>
            <AddressLine>1 Church Lane</AddressLine>
            <AddressLine>Rosebank</AddressLine>
            <AddressLine>Houghton</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Wayne</Firstname>
        <Surname>Farmey</Surname>
        <Telephone>0117453269</Telephone>
        <Address>
            <AddressLine>51 Oak Street</AddressLine>
            <AddressLine>Rivionia</AddressLine>
            <AddressLine>Sandton</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
    <Person>
        <Firstname>Mark</Firstname>
        <Surname>Jones</Surname>
        <Telephone>0119854741</Telephone>
        <Address>
            <AddressLine>4 Arum Lane</AddressLine>
            <AddressLine>Glen Hazel</AddressLine>
            <AddressLine>Johannesburg</AddressLine>
            <AddressLine>South Africa</AddressLine>
        </Address>
    </Person>
</Persons>

我知道我需要使用 .modify()，但是我不确定如何遍历子节点并将每个子节点 "" 插入/附加到父节点中"" 节点.

I know i need to use the .modify(), however i am not sure how to iterate through the child nodes and insert / append each child "<person>" node into the parent "<persons>" node.

我认为它需要类似于以下内容

I would think it would need to be something similiar as below

SET @XMLParentData.modify('
    insert     
        (
            sql:variable("@XMLChildData")
        )
    after
        (/Person[1]/Person[1])
')

SELECT @XMLData

推荐答案

从@XMLChildData 中提取 Person 节点到一个单独的变量，并将其添加到 Persons 节点@XMLParentData.

Extract the Person nodes from @XMLChildData to a separate variable and add that to the Persons node of @XMLParentData.

DECLARE @PersonList XML

SET @PersonList = @XMLChildData.query('Persons/*')

SET @XMLParentData.modify('insert sql:variable("@PersonList") as last into /Persons[1]')

SELECT @XMLParentData

另一种方法是从两个变量中提取 Person 节点并使用 FOR XML PATH 重建 Persons 节点.

Another way is to extract the Person nodes from both variables and rebuild the Persons node using FOR XML PATH.

SET @XMLParentData = (
                     SELECT @XMLParentData.query('/Persons/Person'),
                            @XMLChildData.query('/Persons/Person')
                     FOR XML PATH(''), ROOT('Persons'), TYPE
                     )

相关文章