Insert an XML document into an existing XML document

/*

Though SQL Server 2005 supports xquery, it does not support insert an XML document into another XML document.

The xml data type is supported when creating xml document by FOR XML AUTO, FOR XML RAW, FOR PATH.

E.g., In AdventureWorks database, HumanResources.JobCandidate has an XML column Resume.

*/

USE AdventureWorks

GO

SELECT

JobCandidateID,EmployeeID,Resume,ModifiedDate

FROM

HumanResources.JobCandidate

WHERE

JobCandidateID<3

FOR

XML RAW (‘Candidate’),ROOT(‘Candidates’),ELEMENTS

/*

By means of this feature, we can insert an XML document into an existing xml document.

The following is an example

*/

declare

@x xml;

set

@x=N‘<ManuInstructions>

<Products>

<ProductModelID>1</ProductModelID>

<ProductModelName>SomeBike</ProductModelName>

</Products>

<Locations>

<Location LocationID="L1" >

<Step>Manu step 1 at Loc 1</Step>

<Step>Manu step 2 at Loc 1</Step>

<Step>Manu step 3 at Loc 1</Step>

</Location>

<Location LocationID="L2" >

<Step>Manu step 1 at Loc 2</Step>

<Step>Manu step 2 at Loc 2</Step>

<Step>Manu step 3 at Loc 2</Step>

</Location>

</Locations>

</ManuInstructions>’

— We want to insert a very big xml document with unknown detail structure before <Locations>

declare @x2 xml;

SET

@x2=N‘<ModelDescriptions>

<Spec>

<Color>Blue</Color>

<Weight>2345</Weight>

<Size>23</Size>

</Spec>

<Parts>

<Part ID="130112" Number="10"/>

<Part ID="130113" Number="1"/>

<!– Very Large Xml ….. –>

</Parts>

</ModelDescriptions>’

DECLARE

@xml TABLE (Products xml not null, Locations xml not null, ModelDescription xml not null)

INSERT

@xml (Products,Locations,ModelDescription)

SELECT

@x.query(‘ManuInstructions/Products/*’),

@x.query(‘ManuInstructions/Locations/*’),

@x2.query(‘ModelDescriptions/*’)

SELECT

Products AS "Products", ModelDescription AS "ModelDescription", Locations AS "Locations"

FROM

@xml

FOR

XML PATH(), ROOT(‘ManuInstructions’)

— The output new xml is:

/*

<ManuInstructions>

<Products>

<ProductModelID>1</ProductModelID>

<ProductModelName>SomeBike</ProductModelName>

</Products>

<ModelDescription>

<Spec>

<Color>Blue</Color>

<Weight>2345</Weight>

<Size>23</Size>

</Spec>

<Parts>

<Part ID="130112" Number="10" />

<Part ID="130113" Number="1" />

<!– Very Large Xml ….. –>

</Parts>

</ModelDescription>

<Locations>

<Location LocationID="L1">

<Step>Manu step 1 at Loc 1</Step>

<Step>Manu step 2 at Loc 1</Step>

<Step>Manu step 3 at Loc 1</Step>

</Location>

<Location LocationID="L2">

<Step>Manu step 1 at Loc 2</Step>

<Step>Manu step 2 at Loc 2</Step>

<Step>Manu step 3 at Loc 2</Step>

</Location>

</Locations>

</ManuInstructions>

*/

Advertisements
This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s