using parameters in XSLT passed from C# code

Question

I've gotten this task of transforming an xml into another xml while parameters for the transformation are needed from multiple SQL server tables.

For example, the input XML has a product "XPOZER_90X120".

The XML:

<product exportName="XPOZER_90X120">
      <product-options>
        <option productOptionRef="xpozerFrame">
          <plan>
            <period startDate="2015-10-01">
              <price name="true" netBasePrice="34"/>
              <price name="false" netBasePrice="0"/>
            </period>
          </plan>
        </option>
      </product-options>
    </product>

This product has an option 'productOptionRef="xpozerFrame"' with two childnodes ''. One node has option 'true' with netbaseprice 34 and the other 'false' with netbaseprice 0. This will have to transform into two products, the one without a frame and one with a frame and the names of the two products have to be generated from a table in a database.

xpozerFrame has two records, one with false and one with true. false has 'WISSEL_' attached and true has an empty string attached.

xpozerFrame false WISSEL_

xpozerFrame true ''

Then there's another table where one record has xpozerFrame to be replaced with the values from the previous table.

XPOZER_90X120 XPOZER_[xpozerFrame]90X120

So for xpozerframe = true, XPOZER_90X120 stays the same because of the empty string. And for xpozerframe = false, XPOZER_90X120 becomes XPOZER_WISSEL_90X120

The resulting XML will have to look like:

<products>
    <product exportName="XPOZER_WISSEL_90X120">
    </product>
    <product exportName="XPOZER_90X120">
    </product>
</products>

I hope it's clear so far. Now XSLT alone is not enough for this purpose as it's not possible to read a database from that. So I figured I'd create a C# assembly to be used from Sql server which does the transformation while also reading the tables.

From this link I created the transform code:

http://blogs.msdn.com/b/mrorke/archive/2005/06/28/433471.aspx

public class XsltTransform
{
    public static SqlXml Transform(SqlXml inputDataXML, SqlXml inputTransformXML)
    {
        MemoryStream memoryXml = new System.IO.MemoryStream();
        XslCompiledTransform xslt = new XslCompiledTransform();
        XmlReader output = null;

        xslt.Load(inputTransformXML.CreateReader());

        // Output the newly constructed XML
        XmlTextWriter outputWriter = new XmlTextWriter(memoryXml, System.Text.Encoding.Default);
        xslt.Transform(inputDataXML.CreateReader(), null, outputWriter, null);
        memoryXml.Seek(0, System.IO.SeekOrigin.Begin);
        output = new XmlTextReader(memoryXml);

        return new SqlXml(output);
    }
}

and then do this in SQL server:

alter assembly XsltTransform
from 'c:\temp\XsltTransform.dll'

alter function ApplyXsltTransform( @inputXML xml, @inputTransform xml )
returns xml
as external name XsltTransform.XsltTransform.Transform

declare @xml  xml
declare @xslt xml

select @xml  = BulkColumn from openrowset( Bulk 'C:\temp\20349600_BE_main.xml', SINGLE_BLOB ) as x 
select @xslt = BulkColumn from openrowset( Bulk 'C:\temp\BE_main.xsl', SINGLE_BLOB ) as x 

select dbo.ApplyXsltTransform( @xml, @xslt )

This does the transformation fine but what I couldnt find any information on is how to pass the table values as parameters to the XSLT code and how to use them from within the XSLT code. there is an XsltArgumentlist parameter on the Transform member:

xslt.Transform(inputDataXML.CreateReader(), /*XSLTArgumentList*/, outputWriter, null);

But how to access the arguments in the XSLT?

You might want to add a "parameter" node to your XML and set the parameters within your C# code. Or you might want to read this: stackoverflow.com/q/17579665/5089204 — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 23, 2015 at 7:41

Dan Field · Accepted Answer · 2015-10-23 13:22:19Z

2

In your XSLT template, add a node like:

<xsl:param name="param1" />

This can then be accessed in the XSLT like so:

<xsl:value-of select="$param1" />

And passed from C# like so:

XsltArgumentList args = new XsltArgumentList();
args.Add("param1", null, "DataToPass");

In your case, DataToPass would actually be your values from SQL.

answered Oct 23, 2015 at 13:22

Dan Field

21.8k6 gold badges60 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Serve Laurijssen Over a year ago

yes but the parameter names are generated from a database table so I can't hardcode the names in there.

Dan Field Over a year ago

The paramter names or parameter values are generated from the DB table? Why on earth would you generate paramter names from a DB table?

Serve Laurijssen Over a year ago

It has already been implemented like that. it's a super complex solution for a simple problem but I can't change that

Dan Field Over a year ago

Well in that case, you'd have to read the paramater names from the DB, and then in your XsltArgumentList.Add method you'd have to pass the correct parameter name and value.

Collectives™ on Stack Overflow

using parameters in XSLT passed from C# code

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related