在SQL2000查询中使用XDR的例子

2024-07-21 02:12:15

字体：大中小

来源：转载

供稿：网友

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>

上一篇：如何捕捉temp表空间出错的session信息和SQL

下一篇：SqlServer2000中用户自定义数据类型的使用