FOR XML PATH + file structure

Hey there,
I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!
For example:
<children group=""><child firstname="" lastname=""></child>=""><child
firstname=""
lastname=""></child>.......................</children>.........
And I d like to have
<children group="">
<child firstname="" lastname=""></child>="">
<child firstname="" lastname=""></child>
......................
</children>
..........
To give you an example of the query:
SELECT Child.group AS '@.group',
(SELECT firstname AS '@.firstname',
lastname AS '@.lastname'
FROM Collecte_Data_Extract cde
FOR XML PATH('child'), TYPE)
FROM Feed AS Child
FOR XML PATH('children'), TYPE
Any idea?
Many thanks in advanceThe structure is "conserved"; the whitespace is not. Actually what's
happening is IE is adding whitespace (linefeeds and tabs) to display your
XML. If you want to format your XML in Visual Studio, create a new XML
file, copy and paste your XML into it, and select Edit > Advanced > Format
from the main menu. The extra linefeeds and tabs are irrelevant to your XML
parser (or at least should be).
"Vins" <vbellet@.gmail.com> wrote in message
news:1165406662.637039.25890@.f1g2000cwa.googlegroups.com...
> Hey there,
> I used the FOR XML PATH feature but the XML result is in only one line,
> it does not conserve the XML structure (treeview). When I open the XML
> file with IE, it s working very (I have the XML hierarchy) but when I
> use Visual Studio or other tool to edit it, I ve just one line!
>
> For example:
> <children group=""><child firstname="" lastname=""></child>=""><child
> firstname=""
> lastname=""></child>.......................</children>.........
>
> And I d like to have
> <children group="">
> <child firstname="" lastname=""></child>="">
> <child firstname="" lastname=""></child>
> .......................
> </children>
> ..........
>
> To give you an example of the query:
>
> SELECT Child.group AS '@.group',
>
> (SELECT firstname AS '@.firstname',
> lastname AS '@.lastname'
>
> FROM Collecte_Data_Extract cde
>
> FOR XML PATH('child'), TYPE)
>
> FROM Feed AS Child
> FOR XML PATH('children'), TYPE
>
> Any idea?
> Many thanks in advance
>|||In fact the idea is to have the result dynamically.
For example if I choose to open this XML file in NotePad it does work|||"Vins" <vbellet@.gmail.com> wrote in message
news:1165833640.839620.293150@.16g2000cwy.googlegroups.com...
> In fact the idea is to have the result dynamically.
> For example if I choose to open this XML file in NotePad it does work
Huh?

FOR XML PATH + file structure

Hey there,
I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!
For example:
<children group=""><child firstname="" lastname=""></child>=""><child
firstname=""
lastname=""></child>.......................</children>.........
And I d like to have
<children group="">
<child firstname="" lastname=""></child>="">
<child firstname="" lastname=""></child>
.......................
</children>
...........
To give you an example of the query:
SELECT Child.group AS '@.group',
(SELECT firstname AS '@.firstname',
lastname AS '@.lastname'
FROM Collecte_Data_Extract cde
FOR XML PATH('child'), TYPE)
FROM Feed AS Child
FOR XML PATH('children'), TYPE
Any idea?
Many thanks in advance
The structure is "conserved"; the whitespace is not. Actually what's
happening is IE is adding whitespace (linefeeds and tabs) to display your
XML. If you want to format your XML in Visual Studio, create a new XML
file, copy and paste your XML into it, and select Edit > Advanced > Format
from the main menu. The extra linefeeds and tabs are irrelevant to your XML
parser (or at least should be).
"Vins" <vbellet@.gmail.com> wrote in message
news:1165406662.637039.25890@.f1g2000cwa.googlegrou ps.com...
> Hey there,
> I used the FOR XML PATH feature but the XML result is in only one line,
> it does not conserve the XML structure (treeview). When I open the XML
> file with IE, it s working very (I have the XML hierarchy) but when I
> use Visual Studio or other tool to edit it, I ve just one line!
>
> For example:
> <children group=""><child firstname="" lastname=""></child>=""><child
> firstname=""
> lastname=""></child>.......................</children>.........
>
> And I d like to have
> <children group="">
> <child firstname="" lastname=""></child>="">
> <child firstname="" lastname=""></child>
> .......................
> </children>
> ..........
>
> To give you an example of the query:
>
> SELECT Child.group AS '@.group',
>
> (SELECT firstname AS '@.firstname',
> lastname AS '@.lastname'
>
> FROM Collecte_Data_Extract cde
>
> FOR XML PATH('child'), TYPE)
>
> FROM Feed AS Child
> FOR XML PATH('children'), TYPE
>
> Any idea?
> Many thanks in advance
>
|||In fact the idea is to have the result dynamically.
For example if I choose to open this XML file in NotePad it does work
|||"Vins" <vbellet@.gmail.com> wrote in message
news:1165833640.839620.293150@.16g2000cwy.googlegro ups.com...
> In fact the idea is to have the result dynamically.
> For example if I choose to open this XML file in NotePad it does work
Huh?

FOR XML PATH + file structure

Hey there,

I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!

For example:
<children group=""><child firstname="" lastname=""></child>=""><child
firstname=""
lastname=""></child>.......................</children>.........

And I d like to have
<children group="">
<child firstname="" lastname=""></child>="">
<child firstname="" lastname=""></child>
.......................
</children>
...........

To give you an example of the query:

SELECTChild.group AS '@.group',

(SELECT firstnameAS '@.firstname',
lastnameAS '@.lastname'

FROM Collecte_Data_Extract cde

FOR XML PATH('child'), TYPE)

FROM Feed AS Child
FOR XML PATH('children'), TYPE

Any idea?
Many thanks in advanceVins (vbellet@.gmail.com) writes:

Quote:

Originally Posted by

I used the FOR XML PATH feature but the XML result is in only one line,
it does not conserve the XML structure (treeview). When I open the XML
file with IE, it s working very (I have the XML hierarchy) but when I
use Visual Studio or other tool to edit it, I ve just one line!

Trying running the query in Mgmt Studio, and then double-click on the
XML document to open it the XML viewer. If you save from the viewer
you get the format you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland but I want to do this dynamically, it s boring to do this
for each file (it s a daily report)

Other idea?|||Vins (vbellet@.gmail.com) writes:

Quote:

Originally Posted by

Thanks Erland but I want to do this dynamically, it s boring to do this
for each file (it s a daily report)

If it's a daily report, I assume that you run the program from a client.
Then I guess there is a suitable API that you can use to format your XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

For Xml Explicit

I would like how i have to write my stored procedure ... so that i can make a treeview with different nodes ...
I use the for xml explicit ... but i dont know how a can go ahad with this thing
can you help me please ?
thx a lot
cyrexWhat is the structure of your data in sql server ? Will it be displayed as a treeview - if so what language are you using ?