XML OUTPUT - 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

General SQL Server Forums

New to SQL Server Programming

XML OUTPUT

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-08-21 : 17:21:50

If I run this , the out put is an XML String with the datatype of NVARCHAR

SELECT BrokerDeskInternalID FROM BrokerNeutralityRouteList
FOR XML AUTO

the O/P is

<T ID="B0001"/><T ID="B0002"/><T ID="B0003"/><T ID="B0004"/>

Can we get the O/P formated to diff lines like

<T ID="B0001"/>
<T ID="B0002"/>
<T ID="B0003"/>
<T ID="B0004"/>

Thx

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-22 : 05:08:35

One option is to replace the '>' with '>' + carriage return + line feed...


declare @BrokerNeutralityRouteList table (BrokerDeskInternalID varchar(10))
insert @BrokerNeutralityRouteList
          select 'B0001'
union all select 'B0002'
union all select 'B0003'
union all select 'B0004'

declare @x varchar(100)
set @x = (SELECT BrokerDeskInternalID as ID FROM @BrokerNeutralityRouteList T FOR XML AUTO)

select replace(@x, '>', '>' + char(13) + char(10)) as MyXml

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-08-22 : 10:51:18

Thanks Ryan.

This works on SQL Server 2005 , but I am running SQL Server 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources