Retrieving XML from SQL SERVER

Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options '
Can I use a huge template file for representing the whole hierarchy '
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.comIn SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options '
> Can I use a huge template file for representing the whole hierarchy '
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
>|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegroups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>|||Hello Michael,
Sorry it took so long, last w I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
> Does that help?
> Best regards
> Michael