For XML headache - 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 2000 Forums

Transact-SQL (2000)

For XML headache

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-05-10 : 19:59:29

Yeah, just seeing the words "for xml" sort of give me a headache, but this one is particularly frustrating because it's so simple.

I need to generate XML from a denormalized tables. Yeah, yeah, there's a better data structure for this data, but I have to work with this for now.

The data:

create table lstrings (id int identity,english varchar(255),french varchar(255),german varchar(255))

insert into lstrings (english,french,german)
values ('hello','bonjour','guten tag')

insert into lstrings (english,french,german)
values ('goodbye','adieu','auf wiedersehen')

Now, what I need is XML like this (additonal root nodes don't matter either way):


<lstring id="1">
<english>hello</english>
<french>bonjour</french>
<german>guten tag</german>
</lstring>
<lstring id="2">
<english>goodbye</english>
<french>adieu</french>
<german>auf wiedersehen</german>
</lstring>

Am I out of luck due to the lack of normalization? Will I have to use table variables to normalize the data before doing a FOR XML? Beats me, but I'm sure hoping one of you bright people has some idea.

Thanks
-b

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-10 : 20:17:45

Look at the results in text mode in QA...


set nocount on
create table #lstrings (id int identity,english varchar(255),french varchar(255),german varchar(255))

insert into #lstrings (english,french,german)
values ('hello','bonjour','guten tag')

insert into #lstrings (english,french,german)
values ('goodbye','adieu','auf wiedersehen')

--Select * From #lstrings

Declare @xmlStr varchar(8000)

Select @xmlStr = isnull(@xmlStr + '
','') + 
'<lstring id="'+convert(varchar,id)+'">
	<english>' + english + '</english>
	<french>' + french + '</french>
	<german>' + german + '</german>
</lstring>' 
From #lstrings

Select @xmlStr

Drop Table #lstrings
set nocount off

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-05-10 : 20:19:15

Unfortunately, the problem is that the strings themselves may have non-XML legal characters in them, so I really need to use FOR XML so SQL Server will escape them for me.

I could do it application side, of course, and build the XML with a getRows() call and some logic, but it really seems like this should be possible.

Thanks!
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-05-10 : 20:23:02

Of course, the sheer act of posting here seems to have made my brain work. Here's the solution I have working now:

select 1 as Tag,NULL as Parent,
	i as [lstrings!1!i],
	NULL as [english!2!!element],
	NULL as [french!3!!element],
	NULL as [german!4!!element]
from lstrings

union all

select 2 as Tag, 1 as Parent,
	l1.i as [lstrings!1!i],
	l2.english as [english!2!!element],
	NULL as [french!3!!element],
	NULL as [german!4!!element]
from lstrings l1
join lstrings l2 on l1.i=l2.i

union all

select 3 as Tag, 1 as Parent,
	l1.i as [lstrings!1!i],
	NULL as [english!2!!element],
	l2.french as [french!3!!element],
	NULL as [german!4!!element]
from lstrings l1
join lstrings l2 on l1.i=l2.i

union all

select 4 as Tag, 1 as Parent,
	l1.i as [lstrings!1!i],
	NULL as [english!2!!element],
	NULL as [french!3!!element],
	l2.german as [german!4!!element]
from lstrings l1
join lstrings l2 on l1.i=l2.i

order by [lstrings!1!i]

FOR XML EXPLICIT

Cheers
-b

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-10 : 20:25:18

You don't need the union, you could do it as one query. Here's a Northwind example you could modify pretty easily:

select 1 as tag, null as parent, customerid as [customer!1!customerID],
companyname as [customer!1!company!element],
contactname as [customer!1!contact!element],
contacttitle as [customer!1!title!element]
from customers
for xml explicit

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-05-10 : 20:28:09

Er, yeah. That's a little simpler.

Thanks, Rob!

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources