XML Data Type Modification - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML Data Type Modification

Author

Topic

venkat09
Starting Member

17 Posts

Posted - 2009-12-16 : 08:12:13

Hi all,

Some one please help me.
I am new to SQL server 2k5. I encountered a difficulty working with xml datatype.

Let's say there are two tables BLA_BLA1(Id int, Name nvarchar(50)) and BLA_BLA2(MemberId int, EMail nvarchar(50))

I'll write the code here.

declare @XML xml
declare @XML2 xml

set @XML = (select top 2 * from BLA_BLA1 for XML Auto, root('tables'), Elements)

set @XML2 = (select top 1 * from BLA_BLA2 for XML Auto, Elements)

Then @XML will look like:

<tables>
<BLA_BLA1>
<Id>1</Id>
<Name>Admin</Name>
</BLA_BLA1>
<BLA_BLA1>
<Id>2</Id>
<Name>Nobody</Name>
</BLA_BLA1>
</tables>

and @XML2 would look like:

<BLA_BLA2>
<MemberId>1</MemberId>
<EMail>admin@xyz.com</EMail>
</BLA_BLA2>

What I would like to do is I want to add another child node @XML2 (as it is) to the root node 'tables' in @XML variable.

I couldn't find any useful resource.

Are there any methods to modify XML datatype in SQL server 2005?

Thanks in advance,
Venkat R. Prasad.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-16 : 08:20:50

Put a join over here

declare @XML xml

set @XML = (select top 2 BLA_BLA1.*,BLA_BLA2.* from BLA_BLA1 inner join
BLA_BLA2 on BLA_BLA1.id=BLA_BLA1.memberid for XML Auto, root('tables'), Elements)

print cast(@XML as varchar(max))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

venkat09
Starting Member

17 Posts

Posted - 2009-12-16 : 08:44:50

Hi Senthil,

Thank you for the quick reply. :)
The problem is, the tables are not not necessarily related.

I just want the final xml string to look like

<tables>

<BLA_BLA1>
<Id>1</Id>
<Name>Admin</Name>
</BLA_BLA1>
<BLA_BLA1>
<Id>2</Id>
<Name>Nobody</Name>
</BLA_BLA1>

<BLA_BLA2>
<MemberId>1</MemberId>
<EMail>admin@xyz.com</EMail>
</BLA_BLA2>

</tables>

How can that be done?

(In reality, I have more than 10 tables, whose data, I have to get in an XML string)

Thanks again in advance,
Venkat R. Prasad.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-16 : 10:34:45

Like this?

declare @xml1 xml
declare @xml2 xml
declare @xml3 xml

set @xml1 = '<tables><BLA_BLA1>
<Id>1</Id>
<Name>Admin</Name>
</BLA_BLA1>
<BLA_BLA1>
<Id>2</Id>
<Name>Nobody</Name>
</BLA_BLA1></tables>'

set @xml2 = '<BLA_BLA2>
<MemberId>1</MemberId>
<EMail>admin@xyz.com</EMail>
</BLA_BLA2>'

set @xml3 = (select @xml1.query('/tables/*'), @xml2 for xml path(''), root('tables'))
select @xml3

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources