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

SQL Server Development (2000)

For xml

Author

Topic

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2003-10-23 : 09:07:04

Hi,
how do convert the data like
G0001----- xxx1
G0001----- xxx2

G0002------ xxx3
G0002 ------xxx4

to

<G0001>
<F1 ="xxx1"><F2="xxx2"></>
<G0002>
<F1 ="xxx3"><F2="xxx4"></>

using for xml explicit.
I basically want to convert features detail to xml format,

Thanx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 13:08:05

You might get people to respond if you provide DDL and DML. This thread might go unanswered otherwise.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-23 : 13:45:14

/*
Your output request is not well formed XML
Based on assumptions that your data is organized in such a manner

featureid detail
-------------------- ------------------------------
G0001 xxx1
G0001 xxx2
G0002 xxx3
G0002 xxx4

I believe you will have difficulty using "for XML explicit" to achieve your
desired results because of the dynamic element reference names (F1,F2,...,Fn).
I have provided an alternative procedure which I hope will be of some use
to produce output similar to what I presume you have requested.

*/

set nocount on
drop table #fd
go
create table #fd(featureid varchar(20),detail varchar(30))
go
insert into #fd select 'G0001','xxx1'
insert into #fd select 'G0001','xxx2'
insert into #fd select 'G0002','xxx3'
insert into #fd select 'G0002','xxx4'
go

select * from #fd

declare feature_cursor cursor for
select distinct featureid from #fd

declare @featureid varchar(20),
@xml varchar(8000),
@count int

set @xml = '<root>'
OPEN feature_cursor
FETCH NEXT FROM feature_cursor
INTO @featureID

WHILE @@FETCH_STATUS = 0
BEGIN

select @xml = @xml + '<' + @featureid + '>'
set @count = 1

declare detail_cursor cursor for
select distinct detail from #fd where featureid = @featureid
declare @detail varchar(30)

OPEN detail_cursor

FETCH NEXT FROM detail_cursor
INTO @detail

WHILE @@FETCH_STATUS = 0
BEGIN
select @xml = @xml + '<F' + cast(@count as varchar(10)) + ' detail="' + @detail + '"/>'
set @count = @count + 1

FETCH NEXT FROM detail_cursor
INTO @detail

END

CLOSE detail_cursor
DEALLOCATE detail_cursor

set @xml = @xml + '</' + @featureid + '>'

FETCH NEXT FROM feature_cursor
INTO @featureID

END
CLOSE feature_cursor
DEALLOCATE feature_cursor

select @xml = @xml + '</root>'
select @xml as xml

--OUTPUT
/*
xml

<root>
<G0001>
<F1 detail="xxx1"/>
<F2 detail="xxx2"/>
</G0001>
<G0002>
<F1 detail="xxx3"/>
<F2 detail="xxx4"/>
</G0002>
</root>
*/

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2003-10-25 : 14:14:28

great solution..........

Thanx ehorn.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources