Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 68-Merging two XML documents in TSQL using XQuery

Apr 15 2013 12:00AM by Jacob Sebastian   

This post is triggered by a question I recently saw in the BR Forum asking ideas for merging two XML documents. I thought of adding another installment to the XQuery Labs series to explain how this can be done from TSQL when required.

The examples given below use the modify() method exposed by the XML data type to perform an insertion at the desired location. The insert location can be controlled by the directives before and after. The example given below inserts the XML fragment (“@x2”) before the first element (thus the newly inserted element becomes the first element).

DECLARE @x1 XML, @x2 XML
SELECT @x1 = '
<employees>
	<employee name="Jacob" />
</employees>
'

SELECT @x2 = '
<employee name="Mike" />
'

SET @x1.modify ('
	insert sql:variable("@x2") before (/employees/employee)[1]
')

SELECT @x1
/*
<employees>
  <employee name="Mike" />
  <employee name="Jacob" />
</employees>
*/

The next example inserts the XML fragment after the first element, thus the newly inserted element is inserted at position 2. 

DECLARE @x1 XML, @x2 XML
SELECT @x1 = '
<employees>
	<employee name="Jacob" />
</employees>
'

SELECT @x2 = '
<employee name="Mike" />
'

SET @x1.modify ('
	insert sql:variable("@x2") after (/employees/employee)[1]
')

SELECT @x1
/*
<employees>
  <employee name="Jacob" />
  <employee name="Mike" />
</employees>
*/

It is usually easy to insert an XML element as the first item, because we can do an insert before element 1. However it will be a bit trickier when you want to insert an element as the last item. To insert an element as the last item, we need to identify the last element and do an insert after the last item. The following example demonstrates that.

DECLARE @x1 XML, @x2 XML
SELECT @x1 = '
<Employees>
	<Employee Name="Jacob"/>
	<Employee Name="Steve"/>
</Employees>
'

SELECT @x2 = '
<Employee Name="Mike"/>
'

SET @x1.modify ('
	insert sql:variable("@x2") after (/Employees/Employee)[last()]
')

SELECT @x1
/*
<Employees>
  <Employee Name="Jacob" />
  <Employee Name="Steve" />
  <Employee Name="Mike" />
</Employees>
*/

All these examples assume that there are one or more sibling elements under the element we are trying insert the XML fragment. The following example shows how to insert an XML fragment into an empty element. Note that we are using into (and not before or after) in this example.

DECLARE @x1 XML, @x2 XML
SELECT @x1 = '
<employees />
'

SELECT @x2 = '
<employee name="Mike" />
'

SET @x1.modify ('
	insert sql:variable("@x2") into (/employees)[1]
')

SELECT @x1
/*
<employees>
  <employee name="Mike" />
</employees>
*/

Hope this helps.

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Tags: 


Jacob Sebastian
1 · 100% · 32235
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Jacob, very nice article & very well explained

    My question is, XML being case-sensitive in your sample code & sample data above, some places you have "E" upper case & some places its lower-case for the word "Employee"

    I think that will cause the XML to fail. Correct?

    commented on Apr 15 2013 8:10PM
    prabhupr70
    433 · 0% · 93
  • You are absolutely right. I made a nasty mistake when I posted the original code samples. I have corrected them. Thanks for catching that.

    commented on Apr 16 2013 12:52PM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

"XQuery Lab 68-Merging two XML documents in TSQL using XQuery" rated 5 out of 5 by 2 readers
XQuery Lab 68-Merging two XML documents in TSQL using XQuery , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]