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

Answer 1

Извлечь Person узлы из @XMLChildData в отдельную переменную и добавьте это к Persons узел @XMLParentData,

DECLARE @PersonList XML

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

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

SELECT @XMLParentData

Другой способ состоит в том, чтобы извлечь Person узлы из обеих переменных и перестроить Persons использование узла FOR XML PATH,

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