SQL Server agrega nodos secundarios XML al nodo primario
Necesito tener un script que pueda insertar / agregar nuevos nodos hijos xml a un nodo padre xml preexistente.
--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>'
Quiero que el resultado final sea:
<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>
Sé que necesito usar .modify (), sin embargo, no estoy seguro de cómo recorrer los nodos secundarios e insertar / agregar cada niño"<person>"
nodo en el padre"<persons>"
nodo.
Creo que debería ser algo similar a lo siguiente
SET @XMLParentData.modify('
insert
(
sql:variable("@XMLChildData")
)
after
(/Person[1]/Person[1])
')
SELECT @XMLData