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>'
I want the end result to be:
<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>
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