Using XSDs - Annotated Schemas
SQLXML gives an extensive chance to work with XSDs
(Xml Schema Definitions). In this article we would look how we can use XSD to
get XML data from SQL Server 2000. If you are using ad hoc queries or stored
procedures to query SQL Server 2000, the columns in the result set are
serialized as attributed in the resulting XML document. There is also an
element-centric mode for returning the XML using NESTED, AUTO or RAW xml modes,
you can do
Select * from Orders FOR XML NESTED, ELEMENTS.
There are tons of variations possible in using Selects
to retrieve XML data from SQL Server. You can view article "
Understanding FORXML".
If you want to change the format of the returned XML, you can use an annotated
schema to define which columns will be expressed as elements, and which columns
will be expressed as attributes.
Annotated schemas let you further modify the result
set using standard XPath. An annotated schema is an XML schema document that
specifies both the tables and columns that you wish to query, and the structure
of the resulting XML. SQLXML 2.0 supported two versions of the schemas, XML
Data Reduced (XDR) and W3 XML Schema Definition (XSD). But in this article we
will concentrate on the XSD mappings.
Defining Namespace
In an XSD schema, annotations are specified by using
the namespace urn:schemas-microsoft-com:mapping-schema. And as
an basic framework the XML must have the following definition:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">...
</xsd:schema>
Using XSD
Moving forward lets take a simple example and look at
how we can use XSDs in practical applications. Here in this example we will
query the Orders table and bring data as XML. Here is the XSD:
<?xml version="1.0"
encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="Order" sql:relation="Orders">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderDate" type="xs:dateTime" />
<xs:element name="ShipName" type="xs:string" />
</xs:sequence>
<xs:attribute name="OrderID"
type="xs:int" />
<xs:attribute name="CustomerID"
type="xs:string" />
<xs:attribute name="EmployeeID"
type="xs:int" />
</xs:complexType>
</xs:element>
</xs:schema>
From the above schema, letme just run you through what
we are expecting from this schema. We are trying to get data from a table
"Orders" (sql:relation attribute) and we alias it as "Orders" (name attribute).
Next as we walk down the XSD we are using a complex type. And it has two
elements and three attributes. ShipName and OrderDate are as elements and
OrdersID, CustomerID and EmployeeID are got back as attributes. Now lets move
to the code that makes tha actual call using this XSD.
Dim cmd As SqlXmlCommand
Dim f As FileStream
Dim connStr As String = "DSN=localserver;initial catalog=northwind"
cmd = New SqlXmlCommand(connStr)
cmd.RootTag = "Orders"
cmd.CommandText = "Order"
cmd.CommandType = SqlXmlCommandType.XPath
cmd.SchemaPath = "..\Orders1.xsd"
f = New FileStream("c:\orders.xml", FileMode.Create)
cmd.ExecuteToStream(f)
f.Close()
When we run the above code we get the output from the
XSD into the file. Note that we have used CommandText as "Orders" remember our
alias. Yes, we are getting all the data of the XML and we are not having any
filter. You can give some custom XPath query and narrow the output. Below is a
sample output from the XML File we just saved.
<?xml version="1.0"
encoding="utf-8" ?>
<Orders>
<Order EmployeeID="5" CustomerID="VINET" OrderID="10248">
<OrderDate>1996-07-04T00:00:00</OrderDate>
<ShipName>Vins et alcools Chevalier</ShipName>
</Order>
<Order EmployeeID="6" CustomerID="TOMSP" OrderID="10249">
<OrderDate>1996-07-05T00:00:00</OrderDate>
<ShipName>Toms Spezialitäten</ShipName>
</Order>
<Order EmployeeID="4" ......
Whew, so much to get an XML. But we have used another
feature that gets bundled with SQLXML to achieve this. You can also see how to
"
Generate XSDs using VS .NET IDE".
|