本文介紹了SQL Server 將 XML 子節點附加到父節點的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我需要一個腳本來將新的 xml 子節點插入/附加到預先存在的 xml 父節點.
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>'
我希望最終結果是:
<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()，但是我不確定如何遍歷子節點并將每個子節點 " 插入/附加到父節點中" 節點.
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
推薦答案
從@XMLChildData 中提取 Person 節點到一個單獨的變量，并將其添加到 Persons 節點@XMLParentData.
Extract the Person nodes from @XMLChildData to a separate variable and add that to the Persons node of @XMLParentData.
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 節點.
Another way is to extract the Person nodes from both variables and rebuild the Persons node using FOR XML PATH.
SET @XMLParentData = (
SELECT @XMLParentData.query('/Persons/Person'),
@XMLChildData.query('/Persons/Person')
FOR XML PATH(''), ROOT('Persons'), TYPE
)
這篇關于SQL Server 將 XML 子節點附加到父節點的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！