XML - XSLT Conversion

Squall

Hi all,

Ive been banging my head againt this one for awhile and cant seem to figure it out.

Basically im trying to do an XSLT conversion(XML to XML) for a project in Visual Studio (C#).

Im new to XSLT so I havent really got a clue what im doing
Basically im grabbing some data from SQL server dropping it in a dataset and then passing it in to an XML doc. Once thats done im trying to use xslt to make some formatting changes to the XML

Heres my C# Code.... I dont think the error is here but you never know

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet results = new DataSet();
        da.Fill(results);

        XmlDataDocument xmlDataDoc = new XmlDataDocument(results);

        XslCompiledTransform trans = new XslCompiledTransform();
        trans.Load("C:\\Products.xsl");

        System.IO.Stream s = System.IO.Stream.Null ;
        trans.Transform(xmlDataDoc, XmlWriter.Create(s));

        XmlDataDocument doc2 = new XmlDataDocument();
        doc2.Load(s);
 
        return xmlDataDoc.DocumentElement;

If I take the XML directly from the DataSet im left with this

<?xml version="1.0" encoding="utf-8" ?> 
- <NewDataSet>
- <Table>
  <Product_ID>213e3580-48e6-4085-a9cf-130771d22cf3</Product_ID> 
  <Product_Name>Office 2007</Product_Name> 
  </Table>
- <Table>
  <Product_ID>c4ff8f2b-8ca7-4ee1-9228-99157f6c9662</Product_ID> 
  <Product_Name>Microsoft Works</Product_Name> 
  </Table>
  </NewDataSet>

What im trying to do is replace the NewDataSet element with Products and all the Table Elements with Product.

Heres my attempt at some XSLT:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>


<xsl:template match="NewDataSet">

	<xsl:element name="Product">
	<xsl:apply-templates select = "/"/>
	</xsl:element>

</xsl:template>

<xsl:template match="Table">

	<xsl:element name="Product">
	<xsl:apply-templates/>
	</xsl:element>

</xsl:template>


</xsl:stylesheet>

Anyone have any suggestions?

Squall

I should probably have mentioned my problem....

When I run the code im getting an error saying missing root element on display of the results.

EDIT-- OK... with the lastest XSLT above - Visual Studip is just hanging on the .Transform code.

Inspector Gadget

Afaik the <xsl:apply-templates select="/"> is causing an infinite loop as you're telling to start processing tags from the root down again despite already being inside that element.

Would something like the following XSLT work better?

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="NewDataSet">
	<Products>
		<xsl:apply-templates select="Table"/>
	</Products>
</xsl:template>
<xsl:template match="Table">
	<Product>
	<xsl:apply-templates/>
	</Product>
</xsl:template>
</xsl:stylesheet>

(The <xsl:element> tag is largely redundant, I think)

Hope this helps,
Gadget

Squall

Cheers for that gadget.

I actually realised what was happening when I had it output to a file and ended up with 1.7GB's of XML

Im new to XSL so im still trying to figure out exactly whats what.

This is what I end up with after using your xsl:

<?xml version="1.0" encoding="utf-8" ?> 
<Products>
<Product>213e3580-48e6-4085-a9cf-130771d22cf3OFFICE 2007</Product> 
<Product>c4ff8f2b-8ca7-4ee1-9228-99157f6c9662MICROSOFT WORKS</Product> 
  </Products>

Thats good but what im trying to figure out is how I preserve the rest of the elements i.e. the Product_Name and Product_ID tags.

Ideally Ide like to be able to pass in some data that says what NewDataSet and Table tags should be changed to. Say for example I want to transform XML that holds Data on a Vendor rather than a product ide like to be able to pass in Vendors and Vendor. Then ide like the xsl to change the necessary NewDataSet and Table tags and leave the rest of the elements untouched.

Im not sure if this is even possible with XSL. Any ideas?

Ginger

You can do that...

If you use the xsl:if and the xsl:include tags you can check the first element of the xml and decide what way the info should be shown.

We use a lot of shared XSL files with common transforms for the likes of euro/dollar symbols and dates in different formats.

Why not instead of trying to format your XML using XSL just format it correctly in the dataset???

eg da.fill(ds,"Products")

In your SQL use the AS keyword to rename the fields to appropriate info..

There is also FOR XML AUTO clause in SQL Server.. Might be a wee bit easier that going down the whole XSL route

Squall

Hi Ginger,

Thanks for the reply.

The reason im not doing the formatting through the dataset is because all database reads are handled by a method in a database manager class that accepts a string holding the SQl query and returns an XML doc built straight from the results. I could include another parameter as a source table but ide rather keep that method as general as possible.

I have managed to format the xml correctly through XSL but it meant creating multiple xsl documents and multiple methods within an XmlTransformer class i.e. If ive pulled out data on projects I call projectTransform in the XmlTransfomer class that goes to the Proejct.xsl file. Obviously this isnt ideal as I have multiple methods and multiple xsl files.

Ill look into those xsl tags and see if I reduce it down to one or two xsl files.

Im not too sure how to go about the SQL server side stuff. Any more advice on that area would be appreciated.

Thanks

Ginger

Personally, you shouldnt embed your SQL in the application but use stored procs because you can change it easier and also it executes faster.

Other than that.. have a look at the books online for XML AUTO and it should give you an idea.

As for the SQL

say you have SELECT * FROM tblProducts WHERE PK=1

use SELECT Pk AS 'ProductID', ProductName As 'Name', ProductType as 'Type' etc etc

This allows you to effectively name the fields differently in your results than what they are in the db...

Squall

Oh sorry.... typo. Im passing in the name of the stored procedure rather than hardcoded SQL.

Ive managed to remove the need for XSL conversions for the moment. I decided to just send in a preformatted data set to the data base read call. It basically has a Name and a Table that I set in the calling procedure.

Its a bit of a compromise as I would have prefered to leave the read method as general as possible... Ill just add an overriding method with a replica of the original for later use.

Anyway. The dataset thing works out quite well. I can now easily control the Root element name and and first level child elements depending on what the calling function is. I also found some flaws with how I was doing things during the whole process so overall its been a positive experience :P

Cheers for the replys lads.. im sure ill be back

Inspector Gadget

Squall wrote:

Cheers for that gadget.

I actually realised what was happening when I had it output to a file and ended up with 1.7GB's of XML

Im new to XSL so im still trying to figure out exactly whats what.

This is what I end up with after using your xsl:

<?xml version="1.0" encoding="utf-8" ?> 
<Products>
<Product>213e3580-48e6-4085-a9cf-130771d22cf3OFFICE 2007</Product> 
<Product>c4ff8f2b-8ca7-4ee1-9228-99157f6c9662MICROSOFT WORKS</Product> 
  </Products>

Thats good but what im trying to figure out is how I preserve the rest of the elements i.e. the Product_Name and Product_ID tags.

Ideally Ide like to be able to pass in some data that says what NewDataSet and Table tags should be changed to. Say for example I want to transform XML that holds Data on a Vendor rather than a product ide like to be able to pass in Vendors and Vendor. Then ide like the xsl to change the necessary NewDataSet and Table tags and leave the rest of the elements untouched.

Im not sure if this is even possible with XSL. Any ideas?

I realise you're more or less done with this now, but the mistake is that that second <xsl:apply-templates/> should (I think) be an <xsl:copy>...

(Sorry, jEdit's XSLT processor is misbehaving on me at the moment, can't verify this easily)

Gadget