Re: XML , schema
From: Graeme Malcolm (graemem_cm_at_hotmail.com)
Date: 07/29/04
- Next message: .NetHelpWanted: "Help with OPENXML"
- Previous message: Rena: "XML , schema"
- In reply to: Rena: "XML , schema"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Jul 2004 16:07:21 +0100
Install SQLXML 3.0
(http://www.microsoft.com/downloads/details.aspx?FamilyID=4C8033A9-CF10-4E22
-8004-477098A407AC&displaylang=en) and use an annotated XSD schema. You can
extract the data in the appropriate XML format through an IIS virtual root,
ADO, or ADO.NET.
Here's an example that uses ADO:
Schema:
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="Categories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName" />
<xsd:element name="ProductList" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Product" sql:relation="Products">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="Categories"
parent-key="CategoryID"
child="Products"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName" />
<xsd:element name="Price" type="xsd:decimal"
sql:field="UnitPrice" />
</xsd:sequence>
<xsd:attribute name="ProductCode" type="xsd:integer"
sql:field="ProductID" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="CategoryID" type="xsd:integer"
sql:field="CategoryID" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
ADO code to extract data:
Const DBGUID_XPATH = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"
Const adExecuteStream = 1024
Const adReadAll = -1
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_XPATH
'Create Stream object for results.
Dim stmXMLout
Set stmXMLout = CreateObject("ADODB.Stream")
'Assign the result stream.
stmXMLout.Open
cmd.Properties("Output Stream") = stmXMLout
'Specify the schema
cmd.Properties("mapping schema") = "Catalog.xsd"
'Specify the XPath
cmd.CommandText = "Catalog"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Move to the beginning of the stream
stmXMLout.Position = 0
WScript.Echo stmXMLout.ReadText(adReadAll)
Hope that helps!
Graeme
--
----
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Rena" <Rena@mail.hongkong.com> wrote in message
news:e5vM0qXdEHA.2520@TK2MSFTNGP12.phx.gbl...
Hi all expert,
I would like to know if there are any way for SQL Server to output
required XML format (attribute / element) base on an schema / mapping file?
I know there is auto xml syntax for XML but could there be some way to
auto feed a XML
schema and export the expected XML as output?
is SQL eligable to do it? or any tools could help?
Thanks alot for help
Rena.
- Next message: .NetHelpWanted: "Help with OPENXML"
- Previous message: Rena: "XML , schema"
- In reply to: Rena: "XML , schema"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
