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" <rj******@gmail.com> wrote in message
news:2f**************************@posting.google.c om...
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.
