在SQL2000查询中使用XDR的例子

xdr不会不知道吧？就是简化的xml-data啦。现在示范一个如何实现这样的查询

select o.orderid, o.orderdate from orders o, customers c,

where o.customerid = c.customerid and c.companyname = ?

code:

<%@ language=vbscript %>

<%

dim sconn

sconn = "provider=sqloledb; data source=(local); initial catalog=northwind; user id=sa;password=;"

dim adoconn

set adoconn = server.createobject("adodb.connection")

adoconn.connectionstring = sconn

adoconn.cursorlocation = 3 aduseclient

adoconn.open

dim adocmd

set adocmd = createobject("adodb.command")

set adocmd.activeconnection = adoconn

adocmd.commandtext = "customer[@companyname=""tortuga restaurante""]"

adocmd.dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"

adocmd.properties("mapping schema") = "orders.xml"

adocmd.properties("base path") = "c:\inetpub\wwwroot\pba\"

adocmd.properties("output stream") = response

response.write "<xml id=mydataisle>"

adocmd.execute , , 1024 adexecutestream

response.write "</xml>"

%>

result:

<xml id="mydataisle">

<customer customerid="tortu" companyname="tortuga restaurante">

<order customerid="tortu" orderid="10276" orderdate="1996-08-08t00:00:00" />

<order customerid="tortu" orderid="10293" orderdate="1996-08-29t00:00:00" />

<order customerid="tortu" orderid="10304" orderdate="1996-09-12t00:00:00" />

<order customerid="tortu" orderid="10319" orderdate="1996-10-02t00:00:00" />

<order customerid="tortu" orderid="10518" orderdate="1997-04-25t00:00:00" />

<order customerid="tortu" orderid="10576" orderdate="1997-06-23t00:00:00" />

<order customerid="tortu" orderid="10676" orderdate="1997-09-22t00:00:00" />

<order customerid="tortu" orderid="10842" orderdate="1998-01-20t00:00:00" />

<order customerid="tortu" orderid="10915" orderdate="1998-02-27t00:00:00" />

<order customerid="tortu" orderid="11069" orderdate="1998-05-04t00:00:00" />

</customer>

</xml>

orders.xml

<?xml version="1.0" ?>

<schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql">

<elementtype name="order" sql:relation="orders">

<attributetype name="customerid" />

<attributetype name="orderid" />

<attributetype name="orderdate" />

<attribute type="customerid" sql:field="customerid" />

<attribute type="orderid" sql:field="orderid" />

<attribute type="orderdate" sql:field="orderdate" />

</elementtype>

<elementtype name="customer" sql:relation="customers">

<attributetype name="customerid" />

<attributetype name="companyname" />

<attribute type="customerid" sql:field="customerid" />

<attribute type="companyname" sql:field="companyname" />

<element type="order">

<sql:relationship key-relation="customers" key="customerid" foreign-key="customerid" foreign-

relation="orders" />

</element>

</elementtype>

</schema>