Re: Can I use an industry standard XSD and ADO.NET / SQLServer to output a conforming XML file

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dino Chiesa [Microsoft] (dinoch_at_online.microsoft.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 00:20:16 -0400

Depending on the schema, there are a couple of ways to do it.
XSL would be one way: you could transform the incoming XML (From ADO.NET
query) into the form you like:

 System.Xml.XmlDataDocument doc= new System.Xml.XmlDataDocument(dataset1);
 System.Xml.Xsl.XslTransform x1= new System.Xml.Xsl.XslTransform ();
 x1.Load(XslFilename);
 MemoryStream ms = new MemoryStream();
 x1.Transform(doc.CreateNavigator(), null, ms);
 ms.Seek(0,System.IO.SeekOrigin.Begin);
 string buf = (new StreamReader(ms)).ReadToEnd();

However, XSL is often not, ahhh, let's say, very approachable. So you may
want to use a tool to design the XSL. Or get the help of an expert. (I
don't know of a way to automatically or mechanically generate an XSL
transform, given a starting XSD and an ending XSD. )

Try www.topxml.com for an intro.

Another option if your database is SQL Server is to use SQL2000's FOR XML
support in your queries. You can shape the output XML exactly as you would
like. Again, though, there is some arcane syntax involved.
For example, here's a query I used to retrieve a bunch of FAQ entries from a
table:

 SELECT 1 AS Tag,
 NULL AS Parent,
 '' as [FaqList!1!],
 NULL as [Category!2!CategoryName!element],
 NULL as [FAQ!3!ID!element],
 NULL as [FAQ!3!timestamp!element],
 NULL as [FAQ!3!Question!element],
 NULL as [FAQ!3!Answer!element]

 UNION ALL
 select 2 as Tag,
  1 as Parent,
 '',
 rtrim(c.[category name]),
 NULL, NULL, NULL, NULL
 FROM faq_categories c

 UNION ALL
 SELECT 3 AS Tag,
 2 AS Parent,
 '',
 rtrim(c.[category Name]),
 rtrim(f.ix),
 f.timestamp,
 rtrim(f.question),
 rtrim(f.answer)
 FROM faq_categories c, faq f
 WHERE c.ix = f.category_ix
 ORDER BY [Category!2!CategoryName!element], [FAQ!3!Question!element]
 FOR XML EXPLICIT

Not a pretty sight, is it?
For more on this, check out
http://www.eggheadcafe.com/articles/20030804.asp

Whether you use XSL or SQLXML, be careful about performance.

-D 

-- 
Dino Chiesa
Microsoft Developer Division
d i n o c h @  OmitThis . m i c r o s o f t . c o m
"Rossco" <rjempson@gmail.com> wrote in message 
news:2fabb773.0410031752.64c9d3a9@posting.google.com...
> We have tentatively agreed with a supplier to exchange order
> information.  The plan is to use an 'industry standard' order schema
> definition from say www.xcbl.org.
>
> Is there are simple way to use ADO.NET in this scenario.
> Ideally (naively) I would like to do something along these lines :
>
> 1.  Create a SQLServer stored proc that returns a dataset (or similar)
> containing the order details such as :
>
> select PurchaseOrderID,ProductID,Qty
> from PurchaseOrder
> join PurchaseOrderItem......
>
> 2.  In a COM+ object (not entirely relevant to the problem, but this
> is how it will work), I would expect to create a ADO.NET dataset
> populated from a RPC to the above proc.
>
> 3.  I would assume I need to tell ADO.NET about the xsd, and perhaps
> provide some sort of field mapping?
>
> 4.  I would then like to call some sort WriteXML method on the dataset
> object
>
> 5.  To complete the scenario, I expect I would then 'post' the XML
> file to the supplier's web service.
>
>
> I don't have a vast experience with XML, but I am comfortable that I
> could (and may have to) code this manually, perhaps using the
> XMLDocument class in the .net framework.  I am at the stage of
> exploring my options, and don't want to re-invent the wheel if the
> functionality already exists.
>
> Regards.

Relevant Pages