|
Home > Archive > MS SQL Server > February 2006 > How to simplify XML Query? (SQL2000)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to simplify XML Query? (SQL2000)
|
|
| Greg Bacchus 2006-02-07, 3:23 am |
| Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of the
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
...
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the & #91;Element!Num!Attr
!xml] explicit column name,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!! :(
Is there any way to write a reasonably simple query to get the above schema??
Thanks
Greg
| |
| John Bell 2006-02-07, 3:23 am |
| Hi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
http://groups.google.com/group/micr...af8fbde6?hl=en&
John
"Greg Bacchus" wrote:
> Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of the
> schema I want to produce is:
>
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
>
> The thing is, there are quite a few of these child "Node"s, which will only
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
>
> I was quite excited to find the & #91;Element!Num!Attr
!xml] explicit column name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!! :(
>
> Is there any way to write a reasonably simple query to get the above schema??
> Thanks
> Greg
| |
| Greg Bacchus 2006-02-07, 3:23 am |
| Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[color=darkred]
> Hi Greg
>
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> data in this format. A different approach you be to apply a transform after
> the data has been extracted. You could use XSLT to do this, possibly use the
> document()
> function to include and export the data in fragments.
>
> http://groups.google.com/group/micr...af8fbde6?hl=en&
>
> John
>
> "Greg Bacchus" wrote:
>
| |
| John Bell 2006-02-07, 7:23 am |
| Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
..NET
John
"Greg Bacchus" wrote:
[color=darkred]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
>
> "John Bell" wrote:
>
|
|
|
|
|