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...
>