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

Question

我需要一个脚本，可以将新的XML子节点插入/附加到一个预先存在的XML父节点中。

--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()方法，但我不确定如何迭代子节点并将每个子节点的"<person>"标签插入或追加到父节点"<persons>"中。我认为下面的代码应该是类似的：

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

SELECT @XMLData

- user3917837

Answer 1

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

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节点。

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

- Mikael Eriksson

---

谢谢Mikael，如果我采用你的第一个代码片段，我会遇到与Bummi建议相同的问题，即似乎将子节点中的<persons>列表附加到父<persons>列表中。我希望只有父xml具有< persons >根节点，并仅将子xml中的< person >节点附加到父xml中。如果我采用您的第二个代码片段，它可以按预期工作。如何使用您的第一个代码片段实现所需的结果？我希望了解我做错了什么，以便更好地提高自己。 - user3917837

@user3917837 第一个按预期工作。你可能只是忘记在 sql:variable 中更改变量使用的内容。请查看此 SQL Fiddle。 - Mikael Eriksson