Re: XML , schema

• Previous message: Dan Coates: "For XML Explicit"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 3 Aug 2004 09:33:47 -0700

The link in Graeme's post didn't work for me. The latest version of SQLXML
3.0 SP2 is available for download at:

http://www.microsoft.com/downloads/details.aspx?FamilyID=4c8033a9-cf10-4e22-8004-477098a407ac&displaylang=en

You can extract relational data as XML using the Annotated XSD (AXSD) as
Graeme described and you can also use the AXSD to insert data using Xml
BulkLoad and insert, update, and delete relational data using Diffgrams and
Updategrams.

In addition to the documentation that comes with SQLXML 3.0 you can look at
http://msdn.microsoft.com/sqlxml for more information on the technology

Thanks,
Adam Wiener [MSFT]

"Graeme Malcolm" <graemem_cm@hotmail.com> wrote in message
news:upiCB3XdEHA.3528@TK2MSFTNGP12.phx.gbl...
> 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.
>
>
>
>

• Previous message: Dan Coates: "For XML Explicit"
• Messages sorted by: [ date ] [ thread ]