CODEWITHSUNDEEP
sql-serverxmlxquery
rsnagaraj6in
rsnagaraj6in

Reputation: 45

SQL server Merge two xmls into a third

I am working in SQL Server 2008. I have output from for xml explicit queries in two xml variables. My SP has to combine both the xmls into a well formed third xml. Illustration given below. No complex processing is involved.

I like to merge two xmls present in two xml variables into a single xml

Suppose First XML (@x)

<Student>
<Name>Lawy</Name>
<class>form1</class>
</Student>
<Student>
<Name>Vina</Name>
<class>form2</class>
</Student>

Second XML (@y)

<Dept>
<DeptName>Social</DeptName>
<centre>AOS</centre>
</Dept>
<Dept>
<DeptName>Bio</DeptName>
<centre>RJS</centre>
</Dept>

The resultant I want is (@z)

<sp>
  <Student>
   <Name>Lawy</Name>
   <class>form1</class>
  </Student>
  <Student>
   <Name>Vina</Name>
   <class>form2</class>
  </Student>
  <Dept>
   <DeptName>Social</DeptName>
   <centre>AOS</centre>
  </Dept>
  <Dept>
   <DeptName>Bio</DeptName>
   <centre>RJS</centre>
  </Dept>
</sp>

Upvotes: 1

Views: 1168

Answers (1)

roman
roman

Reputation: 117560

declare @x xml
declare @y xml

select @x = '<Student>
<Name>Lawy</Name>
<class>form1</class>
</Student>
<Student>
<Name>Vina</Name>
<class>form2</class>
</Student>'

select @y = '<Dept>
<DeptName>Social</DeptName>
<centre>AOS</centre>
</Dept>
<Dept>
<DeptName>Bio</DeptName>
<centre>RJS</centre>
</Dept>'

select @x, @y for xml path('sp')

update: if you need attributes in your sp, you can do this

select 2 as [@id], @x, @y for xml path('sp')

Upvotes: 4

Related Questions