FOR XML PATH Question - Nesting Elements

Hi,
I was wondering if anyone can please help me?...I am trying to produce an
XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
nested as childs of a 'bullets' element. Each bullet (to a maximum of 10)
is represented by a field in the database that is named as follows;
field_b1, field_b2, field_b3, ....etc to field_b10
I am using the below statement to produce the XML which currently only works
when I only specify 1 attribute value eg. <bullets><bullet
id="1">Parking</bullet><bullets>;
select top 1
field_id as '@.id',
field_name as 'address/name',
field_street as 'address/street',
field_town as 'address/town',
field_county as 'address/county',
field_pc as 'address/postcode',
field_price as 'price/@.value',
field_stat as 'price/status',
field_pq as 'price/qualifier',
1 as 'bullets/bullet/@.id',
field_b1 as 'bullets/bullet'
from data
where field_id = 9999999
for xml path('property'), root('info')
Which produces;
<info>
<property id="9999999">
<address>
<.... />
<.... />
etc
</address>
<price value="999999">
<... />
<... />
</price>
<bullets>
<bullet id="1">Converted Flat</bullet>
</bullets>
</property>
</info>
If I try to add ;
2 as 'bullets/bullet/@.id',
field_b2 as 'bullets/bullet'
to my statement to create the nested node with a different ID and value it
does not work. Does anyone know of a work around / solution?
Many thanks,
Pete
If I understood your problem correctly something like below should work for
you:
SELECT
1 as "bulets/bulet",
NULL as "bulets/dummy_elt",
2 as "bulets/bulet",
NULL as "bulets/dummy_elt",
3 as "bulets/bulet"
FOR XML PATH('many_bullets')
The NULL columns break the FOR XML PATH groupping logic.
This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if anyone can please help me?...I am trying to produce an
> XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
> nested as childs of a 'bullets' element. Each bullet (to a maximum of
> 10) is represented by a field in the database that is named as follows;
> field_b1, field_b2, field_b3, ....etc to field_b10
> I am using the below statement to produce the XML which currently only
> works when I only specify 1 attribute value eg. <bullets><bullet
> id="1">Parking</bullet><bullets>;
> select top 1
> field_id as '@.id',
> field_name as 'address/name',
> field_street as 'address/street',
> field_town as 'address/town',
> field_county as 'address/county',
> field_pc as 'address/postcode',
> field_price as 'price/@.value',
> field_stat as 'price/status',
> field_pq as 'price/qualifier',
> 1 as 'bullets/bullet/@.id',
> field_b1 as 'bullets/bullet'
> from data
> where field_id = 9999999
> for xml path('property'), root('info')
> Which produces;
> <info>
> <property id="9999999">
> <address>
> <.... />
> <.... />
> etc
> </address>
> <price value="999999">
> <... />
> <... />
> </price>
> <bullets>
> <bullet id="1">Converted Flat</bullet>
> </bullets>
> </property>
> </info>
> If I try to add ;
> 2 as 'bullets/bullet/@.id',
> field_b2 as 'bullets/bullet'
> to my statement to create the nested node with a different ID and value it
> does not work. Does anyone know of a work around / solution?
> Many thanks,
> Pete
>
>
|||Another solution is to make the bullet generation a subquery of its own (if
you do not know a priori how many you may have).
Best regards
Michael
"Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
> If I understood your problem correctly something like below should work
> for you:
> SELECT
> 1 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 2 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 3 as "bulets/bulet"
> FOR XML PATH('many_bullets')
> The NULL columns break the FOR XML PATH groupping logic.
> This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
> Regards,
> Eugene
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks for your help, the solutions offered are exactly what I was after!
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Od9QvEgdFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Another solution is to make the bullet generation a subquery of its own
> (if you do not know a priori how many you may have).
> Best regards
> Michael
> "Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
> news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
>

FOR XML EXPLICIT doesnt seem to work for me--HELP NEEDED ASAP!!

Hi,
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.

<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...

</Customer>
</Customers>

Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!

My apologies for the delay in answering....

If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...

Here is the EXPLICIT solution:

select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"

union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers

-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit

and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):

select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')

Best regards
Michael

FOR XML AUTO not producing the proper parent-child hierarchy

I have a query that uses two views that should produce a simple parent-child
XML document however it repeats every
parent (topic) record for each child record. Here's the query:
select topic.id,
topic.name,
topic.sename,
topic.description,
topic.parentsubjectname,
topic.parentcategroyname,
0 WL,
item.id,
item.name,
item.sename,
item.about,
item.avgrating
from dbo.pico_topic_info topic
join dbo.pico_item_info item on topic.id = item.ParentTopicID
where topic.id = 344
order by topic.id, item.id
for xml auto, elements
I expect a document that looks like this:
<topic>
<id>344</id>
<name>Some Topic</name>
..
<item>
<id>123123</id>
<name>item 1 </name>
..
</item>
<item>
<id>543453</id>
<name>item 2</name>
..
</item>
</topic>
However I get a topic element with every item element:
<topic>
<id>344</id>
<name>Some Topic</name>
..
<item>
<id>123123</id>
<name>item 1 </name>
..
</item>
</topic>
<topic>
<id>344</id>
<name>Some Topic</name>
..
<item>
<id>543453</id>
<name>item 2</name>
..
</item>
</topic>
Any ideas why? I've not had this problem before.
--BuddyWell, I figured it out. The description column is an ntext column which app
arently doesn't play well with other columns
when the FOR XML clause is used.
--Buddy
Buddy Ackerman wrote:
> I have a query that uses two views that should produce a simple
> parent-child XML document however it repeats every parent (topic) record
> for each child record. Here's the query:
>
> select topic.id,
> topic.name,
> topic.sename,
> topic.description,
> topic.parentsubjectname,
> topic.parentcategroyname,
> 0 WL,
> item.id,
> item.name,
> item.sename,
> item.about,
> item.avgrating
> from dbo.pico_topic_info topic
> join dbo.pico_item_info item on topic.id = item.ParentTopicID
> where topic.id = 344
> order by topic.id, item.id
> for xml auto, elements
>
> I expect a document that looks like this:
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>123123</id>
> <name>item 1 </name>
> ...
> </item>
> <item>
> <id>543453</id>
> <name>item 2</name>
> ...
> </item>
> </topic>
> However I get a topic element with every item element:
>
>
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>123123</id>
> <name>item 1 </name>
> ...
> </item>
> </topic>
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>543453</id>
> <name>item 2</name>
> ...
> </item>
> </topic>
>
> Any ideas why? I've not had this problem before.
>
> --Buddy

FOR XML AUTO not producing the proper parent-child hierarchy

I have a query that uses two views that should produce a simple parent-child XML document however it repeats every
parent (topic) record for each child record. Here's the query:
select topic.id,
topic.name,
topic.sename,
topic.description,
topic.parentsubjectname,
topic.parentcategroyname,
0 WL,
item.id,
item.name,
item.sename,
item.about,
item.avgrating
from dbo.pico_topic_info topic
join dbo.pico_item_info item on topic.id = item.ParentTopicID
where topic.id = 344
order by topic.id, item.id
for xml auto, elements
I expect a document that looks like this:
<topic>
<id>344</id>
<name>Some Topic</name>
...
<item>
<id>123123</id>
<name>item 1 </name>
...
</item>
<item>
<id>543453</id>
<name>item 2</name>
...
</item>
</topic>
However I get a topic element with every item element:
<topic>
<id>344</id>
<name>Some Topic</name>
...
<item>
<id>123123</id>
<name>item 1 </name>
...
</item>
</topic>
<topic>
<id>344</id>
<name>Some Topic</name>
...
<item>
<id>543453</id>
<name>item 2</name>
...
</item>
</topic>
Any ideas why? I've not had this problem before.
--Buddy
Well, I figured it out. The description column is an ntext column which apparently doesn't play well with other columns
when the FOR XML clause is used.
--Buddy
Buddy Ackerman wrote:
> I have a query that uses two views that should produce a simple
> parent-child XML document however it repeats every parent (topic) record
> for each child record. Here's the query:
>
> select topic.id,
> topic.name,
> topic.sename,
> topic.description,
> topic.parentsubjectname,
> topic.parentcategroyname,
> 0 WL,
> item.id,
> item.name,
> item.sename,
> item.about,
> item.avgrating
> from dbo.pico_topic_info topic
> join dbo.pico_item_info item on topic.id = item.ParentTopicID
> where topic.id = 344
> order by topic.id, item.id
> for xml auto, elements
>
> I expect a document that looks like this:
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>123123</id>
> <name>item 1 </name>
> ...
> </item>
> <item>
> <id>543453</id>
> <name>item 2</name>
> ...
> </item>
> </topic>
> However I get a topic element with every item element:
>
>
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>123123</id>
> <name>item 1 </name>
> ...
> </item>
> </topic>
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>543453</id>
> <name>item 2</name>
> ...
> </item>
> </topic>
>
> Any ideas why? I've not had this problem before.
>
> --Buddy

For XML AUTO

Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
FOR XML produces XML fragments - not documents. You might need to add a root
element to the results in order to make it well-formed.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
|||Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
directive). You need to do it on the client. The ADO and ADO.NET classes
shipped with SQLXML have a RootTag property you can use, or you could use a
template or schema. Have a look at the documentation shipped with SQLXML
3.0.
Chers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||http://www.microsoft.com/downloads/d...displaylang=en
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||The root property is available in the OLEDB provider that ships with SQL
Server 2000. So it is not part of the server-side software but the client
component.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
> http://www.microsoft.com/downloads/d...displaylang=en
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Is this a separate component of SQL!!!
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> a
> ************************************************** **************************
> ************************************************** **************************
>
>
|||Thanks chaps, I'll give it a try...
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23Yv8W1XZEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The root property is available in the OLEDB provider that ships with SQL
> Server 2000. So it is not part of the server-side software but the client
> component.
> Best regards
> Michael
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
ROOT[vbcol=seagreen]
classes[vbcol=seagreen]
use[vbcol=seagreen]
SQLXML[vbcol=seagreen]
but[vbcol=seagreen]
a[vbcol=seagreen]
I[vbcol=seagreen]
************************************************** **************************[vbcol=seagreen]
error[vbcol=seagreen]
-[vbcol=seagreen]
************************************************** **************************
>