XSLT: Get data from a SQL query result

Hi,

I am trying to get a query result into a node set and then extract data from it. Using the code below

  
<xsl:template match="/">
      <DATACOLLECTION>
        <DATA>
          <MOREDATA>
            <xsl:variable name="recs" >
              <xsl:copy-of select="m4nfn:M4NGetDBResult('OUTBOUNDORDETAIL', '', '', 'Default')"/>
            </xsl:variable>
            <xsl:call-template name="GetExtra">
              <xsl:with-param name="result" select="$recs"/>
            </xsl:call-template>
          </MOREDATA>
        </DATA>
      </DATACOLLECTION>
    </xsl:template>

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

Open in new window


- First get the data into variable "recs" using  my own function which returns an XML string. Seems to work fine
- Call a second template "GetExtra" and passing the xml string as a parameter
- in the "GetExtra" template, use the node-set() to output the data

All of this seems to work and give me the result

<?xml version="1.0"?>
<DATACOLLECTION>
  <DATA>
    <MOREDATA>
      <DOCUMENT>
        <EXTRA>
          <OUTBOUNDORDETAIL>
            <CONSIGNEE>IPL</CONSIGNEE>
            <ORDERID>123test</ORDERID>
            <ORDERLINE>1</ORDERLINE>
            <REFERENCEORDLINE>0</REFERENCEORDLINE>
            <SKU>100060-100-27</SKU>
            <INVENTORYSTATUS>AVAILABLE</INVENTORYSTATUS>
            <QTYORIGINAL>72.0000</QTYORIGINAL>
            <QTYMODIFIED>72.0000</QTYMODIFIED>
            <QTYALLOCATED>72.0000</QTYALLOCATED>
            <QTYSOFTALLOCATED>72.0000</QTYSOFTALLOCATED>
            <QTYPICKED>0.0000</QTYPICKED>
            <QTYSTAGED>0.0000</QTYSTAGED>
            <QTYPACKED>0.0000</QTYPACKED>
            <QTYVERIFIED>0.0000</QTYVERIFIED>
            <QTYLOADED>0.0000</QTYLOADED>
            <QTYSHIPPED>0.0000</QTYSHIPPED>
            <EXPLOADEDFLAG>0</EXPLOADEDFLAG>
            <INPUTQTYUOM />
            <INPUTQTY>-1.0000</INPUTQTY>
            <ADDDATE>2015-09-08T12:13:20-04:00</ADDDATE>
            <ADDUSER>Admin</ADDUSER>
            <EDITDATE>2015-09-08T12:13:54-04:00</EDITDATE>
            <EDITUSER>SYSTEM</EDITUSER>
          </OUTBOUNDORDETAIL>
        </EXTRA>
      </DOCUMENT>
    </MOREDATA>
  </DATA>
</DATACOLLECTION>

Open in new window


The problem is that any attempt I am making at getting data from within the node-set (as shown below is not working

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

Open in new window


Not sure where to go from here. I am sure I could tailor my SQL queries to produce the nodes that I want but it would be more elegant to not have to write a new view for every transformation.

Any suggestions?
lavitalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert BormansInformation ArchitectCommented:
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
0
Geert BormansInformation ArchitectCommented:
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"/>
0
lavitalAuthor Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Geert BormansInformation ArchitectCommented:
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
0
lavitalAuthor Commented:
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>

Open in new window

0
Geert BormansInformation ArchitectCommented:
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
1
lavitalAuthor Commented:
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;
        }

Open in new window


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

Open 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>

Open in new window


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

Open 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?
0
Geert BormansInformation ArchitectCommented:
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
0
Geert BormansInformation ArchitectCommented:
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()
1
lavitalAuthor Commented:
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>

Open 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.
0
Geert BormansInformation ArchitectCommented:
Note there is a big difference in how you assign a parameter

<xsl:param name="foo" select="function()"/>

vs

<xsl:param name="foo">
   <xsl:copy-of select="function()"/>
</xsl:param>

vs

<xsl:param name="foo">
   <xsl:value-of select="function()"/>
</xsl:param>

I would always prefer the first option (unless I need the text nodes only)
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert BormansInformation ArchitectCommented:
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
0
lavitalAuthor Commented:
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>

Open 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>

Open 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;
        }

Open 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.
0
lavitalAuthor Commented:
Addresses the exact issues that were brought up.
0
Geert BormansInformation ArchitectCommented:
welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.