SQL Server中的XML子节点追加到父节点
问题描述:
我需要有一个脚本可以插入/新的XML子节点追加到一个预先存在的XML父节点。SQL Server中的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
答
从提取的@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,如果我把你的第一个代码片断我有同样的问题与建议Bummi,是我似乎从附加子节点的列表到父列表。我希望只有父节点xml,其根节点为,并且只将子节点xml中的节点附加到父节点xml中。如果我把你的第二个代码片段按预期工作。我怎样才能达到你的第一个代码片段所需的结果。我想了解我做错了什么,所以我可能会更好。 –
user3917837
2014-10-21 07:03:57
@ user3917837第一个按预期工作。您可能忘记更改您在'sql:variable'中使用的变量。看看这个[SQL Fiddle](http://sqlfiddle.com/#!3/d41d8/40302)。 – 2014-10-21 07:30:41
对不起......你是对的。谢谢Mikael – user3917837 2014-10-21 07:37:34