Format XML output using FOR XML

Question

Post reply

Format XML output using FOR XML

spegase

SSC Journeyman

Points: 96
More actions
April 1, 2009 at 6:43 am

#97548

Hello
I have some problems for format the data output from sql server using FOR XML.
Here is what I would like to do:
- 1 table
- in this table 2 columns, ID (int) + Data (XML)
the records contains an incrementation of the ID, and block of xml data.
e.g.:
ID XML
1 (look at the attachment please)
2
3
I want to call an SP and get a resultat like this
(look at the attachment please)
I tried to use FOR XML PATH('infos') but this include the extra tag "XML". I'm not sure how to do this.
Thank you for the help 🙂

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Two questions:

* Could you please provide your sample data as file?

* Could you please provide your expected output XML as file?

Since now it is hard to help 😉

Greets

Flo

spegase SSC Journeyman Points: 96 More actions · Answer 2

it seams like the code were trunk

please found here as attachment the full question with code 😉

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 3

select id as "info/@count",

xmlcol as "info"

from mytable

for xml path(''),root('infos')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spegase SSC Journeyman Points: 96 More actions · Answer 4

Hi Mark,

Thanks for this first answer.

I didn't knew the "root()" function, this is very interesting 🙂

Your solution is working but it's just answering the half of my problem.

First point: Is it possible to concat the output ? the hole XML is splitted in many records and this is not useful in my case.

Second point:

id as "info/@count"

Is it possible to put here an autoincrementation ?

to have a result with ID=1 to the first subTag, ID=2, to the second subTag etc...

Third point:

The output look like this now (in XML):

infos / info count="1" / info / hello / ..... / \hello / \ info / \info / \infos

Is it possible to incruste the attribute ID into the XML column ?

Thank you again for the help :-))

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 5

I don't quite follow you here, you'll need to post some sample data and expected results

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spegase SSC Journeyman Points: 96 More actions · Answer 6

Hello again,

I provided in attachmenet a more clear and complet file with my current real situation.

If you need more informations to understand my problem, please feel free to ask me.

Thank you 🙂

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 7

Can't see a simple way of doing this, but the query below should work

with cte as (

select id,

dense_rank() over(order by data.value('/doc[1]/@idx','int')) as dr,

data

from mytable)

select c.dr as "@id",

(select c2.data

from cte c2

where c2.dr=c.dr

order by c2.id

for xml path(''),type).query('/data/doc/*')

from cte c

group by c.dr

for xml path('doc'),root('docs'),type

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spegase SSC Journeyman Points: 96 More actions · Answer 8

Hi Mark

Thank you for this crazy answer 🙂

I never used something so complex, in fact never used "WITH" or "DENSE_RANK"

Let me a few hours to see if its solving my problem, thank you

spegase SSC Journeyman Points: 96 More actions · Answer 9

Re.

Its not working 🙁

Please look at the attachment to see the outputs.

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 10

This is what is used to test and it appears to work. Can you post some of your sample data.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spegase SSC Journeyman Points: 96 More actions · Answer 11

Hello back 😉

So about my last reply, I made a mistake when writing back your query and its working but not exactly how I want.

All the xml content are included in only one Doc tag.

Please look at the attachment file.

spegase SSC Journeyman Points: 96 More actions · Answer 12

The data from the table are like this:

ID: int, PK auto inc

Data: XML

data sample in attachment

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 13

From your sample data how do you know which row will appear in which tag? I can't see the "idx" attribute - my query uses this to group the rows.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spegase SSC Journeyman Points: 96 More actions · Answer 14

Hello again,

Thank you again.

In fact "idx" is not an attribute but a column from the table

so I replaced

DENSE_RANK() OVER(ORDER BY data.value('/Document[1]/@idx','int')) AS dr,

by

DENSE_RANK() OVER(ORDER BY ID) AS dr,

and its working better.

The output now is more like the final result its great 🙂

but still something remaning.

Please look at the attachment for details

I provide now a real sample with the exact output expected.

Thank you again for your help