Hierarchical recordset / XML

Hierarchical recordset / XML

Post by Frof » Thu, 02 Nov 2000 18:55:42



Is it possible to populate a hierarchical recordset by using a XML query
(SELECT ... FOR XML AUTO)?
I need some sample code.

 
 
 

Hierarchical recordset / XML

Post by Gert E.R. Draper » Fri, 03 Nov 2000 05:29:33


FOR XML AUTO facts

* Table / View name in database is used for the element name
* Column names are used for attributes, if you use the ELEMENTS directive
column names are represented as sub elements instead
* Supports nested XML output
    * Nesting determined by ordering of columns in SELECT clause
    * Sibling relationships not supported
* You change names by using table and column aliases

Samples:
Hierarchical Query
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
  <sql:query>
    SELECT Customers.CustomerID, OrderID
    FROM Customers, Orders
    WHERE Customers.CustomerID = Orders.CustomerID
    FOR XML auto
  </sql:query>
</root>

Hierarchical Query - Reversed
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
  <sql:query>
    SELECT OrderID, Customers.CustomerID
    FROM Customers, Orders
    WHERE Customers.CustomerID = Orders.CustomerID
    FOR XML auto
  </sql:query>
</root>

You can not do siblings in FOR XML AUTO you need to use FOR XML EXPLICIT.

-GertD


Quote:> Is it possible to populate a hierarchical recordset by using a XML query
> (SELECT ... FOR XML AUTO)?
> I need some sample code.


 
 
 

1. RDO Hierarchical Recordset From XML?

Hi,

I'm trying to get some hierarchical data from an XML document into an ADO
recordset.  Does anyone have any ideas on how to do this.  I took the route
of attempting to create a disconnected shaped recordset in which I could
create all of the fields I require, but I couldn't find a way to do it.  As
my deadline looms any ideas would be very gratefully accepted.

TIA,

Paul.

2. Trouble calculating Length of Stay

3. Hierarchical Recordsets in XML

4. cleint side and registry

5. Pervasive.sql 2000 and Dial up Networking

6. Hierarchical recordsets and XML

7. Generating Relationships

8. Save in XML does not work for hierarchical recordsets

9. How can I build hierarchical recordset from two recordsets

10. Filter on child recordsets in hierarchical recordsets

11. Save recordset as XML/Restore from XML?

12. Persist recordset as XML, or cook XML w/ XMLDSO