avoid wrapping xml - 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)

avoid wrapping xml

Author

Topic

rogdawg
Starting Member

23 Posts

Posted - 2009-11-12 : 21:42:51

In the SQL Server documentation, it says that if you use "for xml path('')" no wrapper will be generated for the data selected. But, I am trying to select an xml column from a table, and the syntax above does not allow me to get the result without it being wrapped in an element with the column's name. Here is my example:


create table #t(id int, xmldata xml)
insert into #t(id, xmldata) values(1, '<item>blah</item>');
insert into #t(id, xmldata) values(2, '<item>something</item>');
insert into #t(id, xmldata) values(3, '<item>yes</item>');
insert into #t(id, xmldata) values(4, '<item>no</item>');
insert into #t(id, xmldata) values(5, '<item>whatever</item>');

select xmldata
from #t where id < 100
for xml path('')

drop table #t

this returns


<xmldata>
  <item>blah</item>
</xmldata>
<xmldata>
  <item>something</item>
</xmldata>
<xmldata>
  <item>yes</item>
</xmldata>
<xmldata>
  <item>no</item>
</xmldata>
<xmldata>
  <item>whatever</item>
</xmldata>

and I simply want


<item>blah</item>
<item>something</item>
<item>yes</item>
<item>no</item>
<item>whatever</item>

This is a simple thing and I cannot figure out what I am doing wrong.
(its late).

thanks for any help you can provide.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-13 : 00:27:02

change this: create table #t(id int, xmldata xml)

to this: create table #t(id int, xmldata varchar(max))

note that you can't perform xpath queries directly if u do this though

rogdawg
Starting Member

23 Posts

Posted - 2009-11-13 : 07:57:56

quote:
Originally posted by russell

change this: create table #t(id int, xmldata xml)

to this: create table #t(id int, xmldata varchar(max))

note that you can't perform xpath queries directly if u do this though

Thank you for your reply.

I don't believe that works either. It returns the same result as above, only not in xml data format. My results look like

<xmldata>& lt;item& gt;blah& lt;/item& gt;</xmldata>
<xmldata>& lt;item& gt;something& lt;/item& gt;</xmldata>
<xmldata>& lt;item& gt;yes& lt;/item& gt;</xmldata>
<xmldata>& lt;item& gt;no& lt;/item& gt;</xmldata>
<xmldata>& lt;item& gt;whatever& lt;/item& gt;</xmldata>

(I put spaces after the ampersands so the editor would not convert to xml, and you could see the actual format returned)

I am beginning to think this is not possible. I think the problem is that the path('') bit avoids wrapping the row in an element tag but, since I am only selecting one column, and the COLUMN is named "xmldata" there is no way to avoid labeling the column as it is returned. I will have come up with some other approach, I believe.

Thanks for your help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-13 : 08:30:08

you need to take out the for xml path clause too.

but why are you concerned with the parent element anyway? if you're storing xml, why not use it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources