XSLT: Get data from a SQL query result

Well, it looks like XML but there is no proof it actually is XML...
When I hear string... are you sure your function generates a nodeset or XML document,
or does it actually is a string?

instead of
     <xsl:value-of select="exsl:node-set($result)/EXTRA/OUTBOUNDORDETAIL[1]/ORDERID" disable-output-escaping="yes"/>
try
     <xsl:value-of select="translate($result, '&lt;&gt;', '[]')" disable-output-escaping="yes"/>
and show us the result

Maybe you have the context wrong or a hidden namespace

try
 <xsl:value-of select="(exsl:node-set($result)//*[local-name() = 'OUTBOUNDORDETAIL'])[1]/*[local-name() = 'ORDERID']" disable-output-escaping="yes"/>

Yes, you are absolutely correct that this is a string - Sorry if I wasn't clear about that (...my own function which returns an XML string).

From other posts I read, I understood that node-set() would turn it into XML if the string was properly formatted. Since in my example the output of node-set seemed correct, I assumed you could do that.
Perhaps I misunderstand what node-set does (the most popular response I got to the Google query "convert string into xml" was to use node-set() - I know, the price you pay for taking shortcuts).

If you know of any better - or actually correct - way to do this it would be great.

P.S I tried your suggestion with the namespace but that didn't work.

Did you try the code from my first suggestion.
with the translate() function
That is important to have a real idea about what your function result actually is

node-set() creates a node-set from a result tree fragment
RTF were an XSLT1 intermediate tree fragment as copied to the result,
and you could put some XML  in the result in a variable, but not access it as XML

node-set() NEVER had the functionality to turn a string into XML
and I don't know a processor that has implemeneted the extension that way.
You might have hit the wrong reference using google
Some XSLT processors have a xml-parse() extension that does that,
but for that I need to know wwhich processor you are using

We might find a better way to get your data from SQL by the way... let's explore step by step

Sorry - yes, I did try your first suggestion - It does format the  escape strings properly but still no luck with the querying the node.

I think your second comment is probably it - node-set simply doesn't do what I thought it would.

Processor: Microsoft Ver. 1.0

With regards to the SQL result, the function that I use to get the data is below.

  <msxml:script implements-prefix="m4nfn" language="C#">
    <msxml:assembly name="System.Windows.Forms" />
    <msxml:assembly name="Made4Net.DataAccess" />
    <msxml:assembly name="Made4Net.Shared" />
    <msxml:using namespace="System.IO" />
    <msxml:assembly name="System.Data" />
    <![CDATA[
        
public string M4NGetDBResult(string pTable, string pField, string pWhere, string connname)
        {
            System.Data.DataTable dt = new System.Data.DataTable(pTable);

            string sql = String.Format("SELECT TOP 10 * FROM {0} ", pTable);
            if (!string.IsNullOrEmpty(pWhere))
            {
                sql += pWhere;
            }
            XmlDocument doc = new XmlDocument();
            Made4Net.DataAccess.DataInterface.FillDataset(sql, dt, false, connname);
            string outxml = "";
            dt.DataSet.DataSetName = "EXTRA";
            outxml = dt.DataSet.GetXml().Replace("&lt;","<").Replace("&gt;",">");
            return outxml;
        }
    ]]>
</msxml:script>

Select allOpen in new window

So, you get things like this with my first suggestion?
[EXTRA]
Yes, than it simply is a text node() and the node-set() will just make it a text node() again

but hey, your function signature is like this
string M4NGetDBResult
so you want it to return a string

have you tried to build the extension function so it returns an node iterator
public XPathNodeIterator M4NGetDBResult ...
And inside you really make an XML object, not a string
Then you can access the result with the nodeset function

I tried the XPathNodeIterator

        public XPathNodeIterator M4NGetDBResult(string pTable, string pField, string pWhere, string connname)
        {
            System.Data.DataTable dt = new System.Data.DataTable(pTable);

            string sql = String.Format("SELECT TOP 10 * FROM {0} ", pTable);
            if (!string.IsNullOrEmpty(pWhere))
            {
                sql += pWhere;
            }
            Made4Net.DataAccess.DataInterface.FillDataset(sql, dt, false, connname);
            string outxml = "";
            dt.DataSet.DataSetName = "EXTRA";
            outxml = dt.DataSet.GetXml();

            StringReader stringReader = new StringReader(outxml);
            XPathDocument xPathDocument = new XPathDocument(stringReader);
            XPathNavigator xPathNavigator = xPathDocument.CreateNavigator();
            XPathExpression xPathExpression = xPathNavigator.Compile("/");
            XPathNodeIterator xPathNodeIterator = xPathNavigator.Select(xPathExpression);

            return xPathNodeIterator;
        }

Select allOpen in new window

But now doing

      <xsl:value-of select="exsl:node-set($result)" disable-output-escaping="yes"/>

Select allOpen in new window

Simply return the data in all the nodes in one long string

<?xml version="1.0"?>
<DATACOLLECTION>
  <DATA>
    <MOREDATA>
      <DOCUMENT>IPL123test10100060-100-27AVAILABLE72.000072.000072.000072.00000.00000.00000.00000.00000.00000.00000-1.00002015-09-08T12:13:20-04:00Admin2015-09-08T12:13:54-04:00SYSTEMIPL123test210100060-401-27AVAILABLE50.000050.000050.00000.00000.00000.00000.00000.00000.00000.00000-1.00002015-09-08T12:43:46-04:00Admin2015-09-08T12:44:12-04:00SYSTEM</DOCUMENT>
    </MOREDATA>
  </DATA>
</DATACOLLECTION>

Select allOpen in new window

And trying to navigate

      <xsl:value-of select="exsl:node-set($result)//OUTBOUNDORDETAIL[1]/ORDERID" disable-output-escaping="yes"/>

Select allOpen in new window

isn't returning anything.

I tried just in case returning XmlDocument and XmlNode with the same result.
It seems that once you get it using the value-of, xslt turns it into a string?

That is a correct assumption
value-of makes it a string
but value-of makes a string out of the element content
If you see the tags show up with value-of, it means you had a string to begin with, not an XML

If you really want to maintain this approach
(call a SQL from within a C# extension function inside an XSLT)
I would build it up slowly
- first try to make an XML and export it as XML, simple <foo> bar </foo>
because inevitably returning an XML from the extension function seems the hardest bit
- after you tested that you can add the sql to the package

However, I would consider other approaches, such as dumping the sql result into an XML file and pick that up in the XSLT using the document() function
If the SQL DB has a http interfgace, you might even get the data from it directly using the document function... or if you have a webserver in your project, add a little webservice that serves the XML and pick that up using document()

After doing some playing with this I got the following:

XPathNodeIterator  works but only if used directly. i.e.

something line this works very well

              <xsl:for-each select="m4nfn:M4NGetDBResultAsIterator('OUTBOUNDORDETAIL', '*', concat('ORDERID=', m4nfn:FormatValue($ORD)), 'Default', 'DOCUMENTS', '/')/DOCUMENTS/OUTBOUNDORDETAIL">
                <DOC OrderID="{ORDERID}">
                  <ORDERLINE>
                    <xsl:value-of select="ORDERLINE"/>
                  </ORDERLINE>
                  <QTY>
                    <xsl:value-of select="QTYORIGINAL"/>
                  </QTY>
                </DOC>
              </xsl:for-each>

Select allOpen in new window

But if you try to assign the result of the function to a value or a parameter, it reverts aback to a string and you can't query it any more.

Since I am more comfortable with Csharp than with xslt I just build the necessary functions to allow different approaches (get a full subtree as a string, get an interator etc.)

First, thank you very much for pointing me in the right direction.

Finally, just out of curiosity, you said:
"If you really want to maintain this approach
 (call a SQL from within a C# extension function inside an XSLT)"
I assume this means there is another way directly from xslt? I know there are SQL extensions but will they work better for what I am looking to do?

Either way, thank you very much for your help.

I would feel uncomfortable to pull in SQL query results in an XSLT through an extension function.
An empty query result might be a DB connection hickup, a poor query, .... it is very hard to catch the exceptions in your connection with the DB. OK, to play a bit around, but I don't consider that a reliable architecture for production work

There are some libraries (Saxon has an extender) that do SQL from within XSLT, but they are not at all at production level (Saxon warns about that actually)

So, if you can, I would really seperate the steps: pull the sql result as XML from the database, use the document() function from within the XSLT to access the query result.... there are a lot of options here, based on your exact technical requirements... I learned (the hard way) that it pays of in the long run to have sequences of processes rather than one XSLT that does all

Thank you very much! this has been very helpful.

You explanation regarding different uses param  did the trick. Sp now it is looking something like this

In the main template use call-template (obviously, ORD1 would normally be pulled from the original document . This was just for testing)

            <!-- Iteration and search using called template with param -->
            <ITERATION2>
              <xsl:call-template name="Build_Iterator">
                <xsl:with-param name="ORD1">123test2</xsl:with-param>
              </xsl:call-template>
            </ITERATION2>

The template to use the iteration

    <!-- Use separate template to build the iterator and select values from it-->
    <xsl:template name="Build_Iterator">
      <xsl:param name="ORD1"></xsl:param>
      <xsl:param name="iterator" select="m4nfn:M4NGetDBResultAsIterator('OUTBOUNDORDETAIL', '*', concat('ORDERID=', m4nfn:FormatValue($ORD1)), 'Default', 'DOCUMENTS', '/')"></xsl:param>
      <!-- Single value -->
      <USER>
        <xsl:value-of select="$iterator/DOCUMENTS/OUTBOUNDORDETAIL[1]/ADDUSER"/>
      </USER>
      <!-- Loop -->
      <xsl:for-each select="$iterator/DOCUMENTS/OUTBOUNDORDETAIL">
        <DOC OrderID="{ORDERID}">
          <ORDERLINE>
            <xsl:value-of select="ORDERLINE"/>
          </ORDERLINE>
          <QTY>
            <xsl:value-of select="QTYORIGINAL"/>
          </QTY>
        </DOC>
      </xsl:for-each>
    </xsl:template>

Select allOpen in new window

I also separated the C# functions into two to allow for more robust error checking in the SQL and to have two options for a regular string and for an iterator.
So the string option would look like this

            <!-- Get one value from string function -->
            <ONENODEVAL>
                <xsl:value-of select="m4nfn:M4NGetDBResultAsString('OUTBOUNDORDETAIL', '*', concat('ORDERID=', m4nfn:FormatValue($ORD)), 'Default', 'DOCUMENTS', 'DOCUMENTS/OUTBOUNDORDETAIL[2]/ORDERID/text()')" disable-output-escaping="yes"/>
            </ONENODEVAL>
            <!-- Get full node from string function -->
            <ONEFULLNODE>

Select allOpen in new window

Note: the iterator function just calls the string function and converts it to an iterator (code is below if you are curious (there are some internal functions here for data access but any standard functions using Datatable() and Dataset() will do the same)

        public XPathNodeIterator M4NGetDBResultAsIterator(string pTable, string pField, string pWhere, string connname, string pSetName, string pxPath)
        {
            string outxml = M4NGetDBResultAsString(pTable, pField, pWhere, connname, pSetName, pxPath);

            StringReader stringReader = new StringReader(outxml);
            XPathDocument xPathDocument = new XPathDocument(stringReader);
            XPathNavigator xPathNavigator = xPathDocument.CreateNavigator();
            XPathExpression xPathExpression = xPathNavigator.Compile("/");
            XPathNodeIterator xPathNodeIterator = xPathNavigator.Select(pxPath);
            return xPathNodeIterator;

        }

        public string M4NGetDBResultAsString(string pTable, string pField, string pWhere, string connname, string pSetName, string pxPath)
        {
            System.Data.DataTable dt = new System.Data.DataTable(pTable);
            XmlDocument oDoc = new XmlDocument();
            XmlNode oNode = null; 
            string outxml = "";
            string sql = String.Format("SELECT {0} FROM {1} ", pField, pTable);
            if (!string.IsNullOrEmpty(pWhere))
            {
                sql += "WHERE " + pWhere;
            }
            try
            {
                Made4Net.DataAccess.DataInterface.FillDataset(sql, dt, false, connname);
                if (dt.Rows.Count == 0)
                {
                    outxml = "<" + pSetName + ">" + outxml + "</" + pSetName + ">";
                    return outxml;
                }
                dt.DataSet.DataSetName = pSetName;
                oDoc.LoadXml(dt.DataSet.GetXml().ToString());
                oNode = oDoc.SelectSingleNode(pxPath);
                if (oNode == null)
                {
                    outxml = "<" + pSetName + ">";
                    outxml += string.Format("xPath {0} did not return results", pxPath);
                    outxml += "</" + pSetName + ">";
                    return outxml;
                }
                outxml = oNode.OuterXml.ToString();
            }
            catch (Exception Ex)
            {
                outxml = "<" + pSetName + ">" + Ex.Message + "</" + pSetName + ">";
            }
            return outxml;
        }

Select allOpen in new window

Your point about multiple documents is well taken. In my case, it may be more difficult because the whole transformation process happen from inside the application but I think the call-template achieves at least part of the goal by nicely separating the steps

Thanks again. I now have all I need.

Addresses the exact issues that were brought up.

welcome