在SQL2000查詢中使用XDR的例子
2024-07-21 02:12:15
供稿：網(wǎng)友
xdr不會(huì)不知道吧？就是簡(jiǎn)化的xml-data啦?，F(xiàn)在示范一個(gè)如何實(shí)現(xiàn)這樣的查詢
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>
