T-SQL and XML question.
-
A website I'm writing uses jQuery and a plugin called Flexigrid, that expects an XML document in this format:
<rows>
<page>1</page>
<total>7</total>
<row id="1">
<cell>1</cell>
<cell>SomeName</cell>
<cell>SomeDescription</cell>
</row>
<row id="2">
<cell>2</cell>
<cell>SomeName</cell>
<cell>SomeDescription</cell>
</row>
</rows>I managed to make it work with the following SQL:
Select
1 As 'page'
,7 As 'total'
,(
Select
ID As '@id'
,(Select ID As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type)
,(Select [Name] As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type)
,(Select [Description] As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type, Elements XsiNil)
From
Table dc
For Xml Path('row'), Type
)
For Xml Path('rows'), TypeIs there a better way to do this? I've scoured Google, and wasn't able to find anything. This works, but it seems like there must be a better way that I'm just not finding. Thanks.
Jamie Nordmeyer
Portland, Oregon, USA
http://www.feralcodemonkies.com