Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL FOR XML AUTO, XMLDATA varies Schema name

Posted on 2004-11-23
13
Medium Priority
?
2,576 Views
Last Modified: 2013-11-19
In reference to this Q:
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21209401.html

How do I deal with SQL Server returning varying Schema names from a query like SELECT ... FROM ... FOR XML AUTO, XMLDATA?

Example fragment from above:
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">

The *generic* XSLT I developed to process any FOR XML AUTO, XMLDATA result needs to refer to that name:
<xsl:stylesheet version="1.01"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:xs="http://www.w3.org/2001/XMLSchema"
      xmlns:msxsl="urn:schemas-microsoft-com:xslt"
      xmlns:xdata="urn:schemas-microsoft-com:xml-data"
      xmlns:dt="urn:schemas-microsoft-com:datatypes"
      xmlns:r="x-schema:#Schema1"
      exclude-result-prefixes="dt xs r msxsl xdata">

This problem appears unfixable, without having to generate the XSL on the fly, thus loosing the performance of a cached stylesheet.
0
Comment
Question by:rherguth
  • 7
  • 6
13 Comments
 
LVL 26

Expert Comment

by:rdcpro
ID: 12658561
Well, you can still select nodes that are scoped to a namespace, even if you don't know what that namespace is.  Actually, in this case you can figure out the actual namespace, but to select the <row> elements in the XML without knowing for sure whether the namespace is x-schema:#Schema1 or even x-schema:#Schema123, you can use XPath expressions based on the local name.  For example:

<xsl:for-each select="*[local-name() = 'row']">

selects the same nodes as:

<xsl:for-each select="r:row">

Admittedly, the XPath is a bit ugly...but it works.  If you had multiple XML documents where there were multiple schema namespaces, you could differentiate between them by setting a parameter before the transformation that contains the namespace-uri you're interested in, and using:

<xsl:for-each select="*[local-name() = 'row'][namespace-uri() = $pMyNamespaceUri]">

Frankly, this is one of the things I don't like about the X-Schema approach that Microsoft used.  XML Namespaces are supposed to be URIs, which uniquely identify the scope of a node.  While x-schema:#Schema1 may be locally unique, it is not globally unique.  Microsoft InfoPath does the same thing, inasmuch as it generates a namespace node that uses the date and time the template was first created.  This is a PITA in designing an application, because now you have to parse the XML to find out the namespace, then attach the namespace using DOM methods to the XSLT, which as you say invalidates a cached XSLT.  In .NET you may be able to use a namespace manager before the transform, but I've never tried this.

The right way would have been to provide a method to specify the namespace-uri in the Query in the first place, perhaps like:

FOR XML AUTO, XMLDATA='myNamespace'

which would return

<Schema name="myNamespace" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
[...]
<row xmlns="x-schema:#myNamespace" HiddenActivityID="925" [...]/>

This would be easy to handle in a generic XSLT.  And then the namespace for the data could be globally unique as well.

Unfortunately, they didn't ask me when they designed it...  ;^)

The good news is you can still use the local-name() predicate to get the nodes without knowing the namespace, as long as there is only one namespace involved in a <row> node.


Regards,
Mike Sharp
0
 
LVL 9

Author Comment

by:rherguth
ID: 12660783
That is much more compact than what I was trying to do with:
...
      xmlns:r="x-schema:#Schema1"
      xmlns:s="x-schema:#Schema2"
      xmlns:t="x-schema:#Schema3"
      xmlns:u="x-schema:#Schema4"
      xmlns:v="x-schema:#Schema5"
      xmlns:w="x-schema:#Schema6"
      exclude-result-prefixes="dt xs r s t u v w msxsl xdata">

      <xsl:variable name="row">
            <xsl:if test="/xml/r:row">
                  <xsl:copy select="msxsl:node-set(/xml/r:row)"/>
            </xsl:if>
            <xsl:if test="/xml/s:row">
                  <xsl:copy select="msxsl:node-set(/xml/s:row)"/>
            </xsl:if>
            ...
      </xsl:variable>

0
 
LVL 9

Author Comment

by:rherguth
ID: 12660875
I have a xsl:choose that appears to necessitate using an apply-templates because I get an error if I try to embed xsl:choose within another xsl:choose.  So I have:

<xsl:template match="*[local-name() = 'row']">

and this appears to get me the node-set that I want.  However, I think you know that SQL does not include an attribute (column) in an r:row if that column is NULL in the DB.  To deal with that, I grab the AttributeType nodes from the Schema section in a xsl:for-each:

<xsl:for-each select="msxsl:node-set(/xml/xdata:Schema/xdata:ElementType/xdata:AttributeType)">

and after much trial and error, I figured out how to grab the matching attribute in the rows that has the same name as the AttributeType in the Schema section:

<xsl:variable name="fieldname" select="@name"/>
<xsl:variable name="fieldvalue" select="/xml/r:row[position() = $ContextRow]/@*[name() = $fieldname]" default=" "/>

And this is where my problem lies.  I'm now trying to figure out how to do this attribute match using the local-name row:

<xsl:variable name="fieldvalue" select="*[local-name() = 'row'][position() = $ContextRow]/@*[name() = $fieldname]" default=" "/>

This doesn't appear to work.  So then I tried creating a variable up before the for-each (that causes the problematic  context node change to AttributeType), but after the <xsl:template match="*[local-name() = 'row']">:

<xsl:variable name="datarow" select="msxsl:node-set(local-name(*[local-name() = 'row'))"/>

Then, modify the value variable to:
<xsl:variable name="fieldvalue" select="$datarow/@*[name() = $fieldname]" default=" "/>

That doesn't work either.  Any ideas?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:rherguth
ID: 12660883
I should explain that $contextrow is defined as the position of the row:

<xsl:variable name="ContextRow" select="position()"/>
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12661149
Actually, I think there's a way easier method of doing this that doesn't require the schema at all.  Correct me if I'm wrong, but you simply want to build a table that has a column for each attribute in the row, realizing of course that some rows won't have an attribute with that name if the column in that row is null.

If so, this is easily handled by Muenchian grouping, although you may get a performance improvement enumerating the columns with the schema anyway.  I have an example of this on my site that uses an ADO recordset with an embedded schema that is sparsely populated just like yours.  Though the xml is flat, the implicit hierarchy in the data itself is much more complex than yours, but illustrates how this sort of thing would be done.  In fact, it would be pretty tricky indeed to built the table using any other approach.

You might find this very interesting.  Check it out at:

http://rdcpro.com/Members/rdcpro/snippets/groupinggrades/

In the meantime, I'll set up a simple example using the data you posted in the other question, and post it here shortly.

Regards,
Mike Sharp
0
 
LVL 26

Accepted Solution

by:
rdcpro earned 2000 total points
ID: 12661213
Ok, here's a simple XSLT that builds a table.  Notice how it handles the row that didn't have a Contractor attribute:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:key name="kColumn" match="@*" use="local-name()"/>
      <xsl:template match="xml">
            <html>
                  <head/>
                  <body>
                        <table border="1" cellpadding="4" cellspacing="0">
                              <tbody>
                                    <tr>
                                          <xsl:for-each select="*[local-name() = 'row']/@*[count( . | key('kColumn', local-name())[1]) = 1]">
                                                <th><xsl:value-of select="local-name()"/></th>
                                          </xsl:for-each>
                                    </tr>
                                    <xsl:for-each select="*[local-name() = 'row']">
                                          <xsl:variable name="vCurrentRow" select="."/>
                                          <tr>
                                                <xsl:for-each select="/xml/*[local-name() = 'row']/@*[count( . | key('kColumn', local-name())[1]) = 1]">
                                                      <td>
                                                            <xsl:if test="not($vCurrentRow/@*[local-name() = local-name(current())])">&#160;</xsl:if>
                                                            <xsl:value-of select="$vCurrentRow/@*[local-name() = local-name(current())]"/>
                                                      </td>
                                                </xsl:for-each>
                                          </tr>
                                    </xsl:for-each>
                              </tbody>
                        </table>
                  </body>
            </html>
      </xsl:template>
</xsl:stylesheet>


Is this where you're headed?  The only reason I can see to keep the schema around is if you're strongly typing any of the nodes.

Regards,
Mike Sharp
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12661243
Oh, I should have mentioned that if it's possible that no row has a certain column attribute, but you still want it in the table, then build the key and the table headings from the schema AttributeTypes the same way as I did it from the row attributes themselves.  

Also, there's a neat technique that another expert here posted recently that can be used to map "readable" names to your schema, without an ugly XSL choose structure.  Here it is:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:map="urn:schemas-rdcpro-com:xslt-mapping">
      <xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:key name="kColumn" match="@*" use="local-name()"/>
      <xsl:template match="xml">
            <html>
                  <head/>
                  <body>
                        <table border="1" cellpadding="4" cellspacing="0">
                              <tbody>
                                    <tr>
                                          <xsl:for-each select="*[local-name() = 'row']/@*[count( . | key('kColumn', local-name())[1]) = 1]">
                                                <th><xsl:value-of select="document('')/xsl:stylesheet/map:item[@key = local-name(current())]/@value"/></th>
                                          </xsl:for-each>
                                    </tr>
                                    <xsl:for-each select="*[local-name() = 'row']">
                                          <xsl:variable name="vCurrentRow" select="."/>
                                          <tr>
                                                <xsl:for-each select="/xml/*[local-name() = 'row']/@*[count( . | key('kColumn', local-name())[1]) = 1]">
                                                      <td>
                                                            <xsl:if test="not($vCurrentRow/@*[local-name() = local-name(current())])">&#160;</xsl:if>
                                                            <xsl:value-of select="$vCurrentRow/@*[local-name() = local-name(current())]"/>
                                                      </td>
                                                </xsl:for-each>
                                          </tr>
                                    </xsl:for-each>
                              </tbody>
                        </table>
                  </body>
            </html>
      </xsl:template>
      <map:item key="HiddenActivityID" value="Hidden Activity ID"/>
      <map:item key="HiddenAgreementID" value="Hidden Agreement ID"/>
      <map:item key="Contract_x0020_Group" value="Contract Group"/>
      <map:item key="Contractor" value="Contractor"/>
      <map:item key="Project_x0020_Engineer" value="Project Engineer"/>
      <map:item key="Project_x0020_Title" value="Project Title"/>
      <map:item key="Task_x0020_Number" value="Task Number"/>
      <map:item key="New_x0020_Mod_x0020_PR" value="New Mod PR"/>
      <map:item key="Task_x0020_Name" value="Task Name"/>
      <map:item key="Actions" value="Actions"/>
      <map:item key="Delete_x0020_Task" value="Delete Task"/>
      <map:item key="HiddenFY" value="Hidden FY"/>
      <map:item key="HiddenCETSerial" value="Hidden CET Serial"/>
</xsl:stylesheet>

which produces:

<html xmlns:map="urn:schemas-rdcpro-com:xslt-mapping">
      <head>
            <META http-equiv="Content-Type" content="text/html; charset=UTF-8" />
      </head>
      <body>
            <table border="1" cellpadding="4" cellspacing="0">
                  <tbody>
                        <tr>
                              <th>Hidden Activity ID</th>
                              <th>Hidden Agreement ID</th>
                              <th>Contract Group</th>
                              <th>Contractor</th>
                              <th>Project Engineer</th>
                              <th>Project Title</th>
                              <th>Task Number</th>
                              <th>New Mod PR</th>
                              <th>Task Name</th>
                              <th>Actions</th>
                              <th>Delete Task</th>
                              <th>Hidden FY</th>
                              <th>Hidden CET Serial</th>
                        </tr>
                        <tr>
                              <td>925</td>
                              <td>747</td>
                              <td>Single Award</td>
                              <td>XYZ</td>
                              <td>Joe TestGuy</td>
                              <td>Blah, Blah</td>
                              <td>ABC123</td>
                              <td>New</td>
                              <td>Blah, Blah</td>
                              <td>View</td>
                              <td>Delete</td>
                              <td>2005</td>
                              <td>A1B</td>
                        </tr>
                        <tr>
                              <td>924</td>
                              <td>745</td>
                              <td>Single Award</td>
                              <td>ABC</td>
                              <td>Joe TestGuy</td>
                              <td>Blah, Blah 2</td>
                              <td>ABC567</td>
                              <td>New</td>
                              <td>Blah, Blah 2</td>
                              <td>View</td>
                              <td>Delete</td>
                              <td>2005</td>
                              <td>D1C</td>
                        </tr>
                        <tr>
                              <td>923</td>
                              <td>744</td>
                              <td>Other</td>
                              <td> </td>
                              <td>Joe TestGuy</td>
                              <td>Blah, Blah 3</td>
                              <td>ABC789</td>
                              <td>New</td>
                              <td>Blah, Blah 3</td>
                              <td>View</td>
                              <td>Delete</td>
                              <td>2005</td>
                              <td>K1D</td>
                        </tr>
                  </tbody>
            </table>
      </body>
</html>


Regards,
Mike Sharp
0
 
LVL 9

Author Comment

by:rherguth
ID: 12670753
>> they didn't ask me when they designed it

I'd encourage you to make your requests known to them during this Beta period.  I know I have been derelict in this respect.

>> The only reason I can see to keep the schema around is if you're strongly typing any of the nodes
>> Oh, I should have mentioned that if it's possible that no row has a certain column attribute, but you still want it in the table, then build the key and the table headings from the schema AttributeTypes the same way as I did it from the row attributes themselves.

Yes, that's my reason for including XMLDATA in the syntax.  I need the schema.  There are times when all rows will contain NULLs for a column, which would cause the column to be dropped from the result.  I had also hoped that there would be a way to tell SQL Server to populate the rest of the optional schema attributes like XML serialized from ADO does.  I haven't found any settings that do that.  I have used the ADO schema attributes to generate data entry forms.  In this case, the data type is used to tell the client-side sorting js to use numeric or text sorting.

>> You might find this very interesting...

I had bookmarked that page, because I need to add unlimited grouping support into the XSLT as the next feature.

>> Also, there's a neat technique that another expert here posted recently that can be used to map "readable" names to your schema, without an ugly XSL choose structure.

I've seen that technique, but it was too data centric.  I'm trying to keep this XSLT generic and cacheable.  I'm using a more brute-force technique:
<xsl:variable name="columndisplayname">
      <xsl:call-template name="replace">
            <xsl:with-param name="string" select="@name"/>
            <xsl:with-param name="pattern" select="'_x0020_'"/>
            <xsl:with-param name="replacement" select="'&#xA0;'"/>
      </xsl:call-template>
</xsl:variable>

<!-- Replace Function -->
<xsl:template name="replace">
      <xsl:param name="string" select="''"/>
      <xsl:param name="pattern" select="''"/>
      <xsl:param name="replacement" select="''"/>
      <xsl:choose>
      <xsl:when test="$pattern != '' and $string != '' and contains($string, $pattern)">
            <xsl:value-of select="substring-before($string, $pattern)"/>
            <!--Use "xsl:copy-of" instead of "xsl:value-of" so that users may substitute nodes as well as strings for $replacement.-->
            <xsl:copy-of select="$replacement"/>
            <xsl:call-template name="replace">
                  <xsl:with-param name="string" select="substring-after($string, $pattern)"/>
                  <xsl:with-param name="pattern" select="$pattern"/>
                  <xsl:with-param name="replacement" select="$replacement"/>
            </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
            <xsl:value-of select="$string"/>
      </xsl:otherwise>
      </xsl:choose>
</xsl:template>

So, in the end I'm trying to use the new tools you've given me to eliminate the for-each on schema columns so that I don't lose the row context.  If I can do a key match without doing a for-each, then I should have what I need.

After reading your XPath, it struck me that I should have been thinking about the problem as a Regular Expression.  That got me to googling for a RegExp to XPath converter.  I ran across this thread regarding RegExp support for XSL:
http://www.biglist.com/lists/xsl-list/archives/200201/msg00361.html

<xsl:regexp-template match="\sqrt{([^{}]*)}">

Ahhh, that looks familiar to me now :)
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12670905
Well you can always use regular expressions in an extension function.  Just make sure that your extension functions are "pure" meaning they don't set or modify global values.  Here's an "Library book Checkout" example I used once to provide DATEADD() functionality.  Given a node with a parseable date, you get back a date that's 14 days in the future:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                    xmlns:dates="urn:rdcpro-com:dates"
                    exclude-result-prefixes="dates msxsl"
                    >
     <xsl:output method="xml" encoding="UTF-8"/>
      <msxsl:script language="JScript" implements-prefix="dates">
            <![CDATA[
                  function parseDate(oNodeList) {
                        var dateString = oNodeList[0].text;
                        var t = Date.parse(dateString);
                        return t;
                  }
                  function addTimePeriod(oNodeList, iDaysToAdd)
                  {
                        var dateString = oNodeList[0].text;
                        var MinMilli = 1000 * 60;
                        var HrMilli = MinMilli * 60;
                        var DyMilli = HrMilli * 24;
                        var t = Date.parse(dateString);
                        return(t + iDaysToAdd * DyMilli);
                  }
                  function formatDate(t) {
                        var oDate = new Date(t);
                        var s = oDate.getFullYear();
                        s += "-";
                        s += oDate.getMonth() + 1
                        s += "-";
                        s += oDate.getDate()
                        return s;
                  }
            ]]>
      </msxsl:script>
      <xsl:template match="Dates">
            <xsl:copy>
                  <xsl:for-each select="CheckedOutDate">
                        <DueDate>
                              <xsl:value-of select="dates:formatDate(dates:addTimePeriod(., 14))"/>
                        </DueDate>
                  </xsl:for-each>
            </xsl:copy>
      </xsl:template>
</xsl:stylesheet>


You can also use the document function to load column names from separate XML document.  A better approach (no latency) would be to add the entire set of column names to a parameter that is set before transforming.  The XSLT is still pre-compiled and cached.  

Also, the example I posted does drop the column if all rows are null.  But if you're using the Schema to generate forms, I can see why it would be needed.  In any case, the muenchian method allows you to either show all the possible attributes, null or not, or show only those where at least one row contains the attribute.

I'm pretty sure there are at least two other ways to prevent the loss of attributes when a column contains a null, if that's what you're after.  Using FOR XML AUTO won't do it, though, AFAIK.  On the other hand, FOR XML EXPLICIT allows you to create arbitrary XML structures, and I'm pretty sure you will get the attribute even if the column is null.  Worth looking at, anyway.  Also, I recall something about using an annotated mapping schema that allows you to define how nulls are treated.  But that may only be for updates...I don't remember exactly.

One nice thing about using FOR XML EXPLICIT is that you could construct an initial row that contained the plain english column heading names...assuming they could be stored in a table in the database.  

Also, you can easily rename the elements in your FOR XML AUTO query, by using aliases.  I regularly do this to eliminate the _x0020_ garbage:

Select [Project Engineer] As ProjectEngineer FROM Foo FOR XML AUTO


>> they didn't ask me when they designed it
}} I'd encourage you to make your requests known to them during this Beta period.  I know I have been derelict in this respect.

My comments were strictly tongue-in-cheek.  And they came up with the idea of embedded schemas some time back...It suddenly showed up one day, and now we're stuck with it. It's almost as bad as the http://tempuri.org that you sometimes see.  ;^)

Regards,
Mike Sharp
0
 
LVL 9

Author Comment

by:rherguth
ID: 12671143
My RegExp comments were related to my tendancy to think of XSL/XPATH procedurally, rather than declaratively.  For some reason (maybe it's the inherrantly procedural implication of xsl:for-each), I don't completely swicth gears when doing XSL, like I do when defining RegExps or SQL.  So if I could solve the problem of figuring out what nodes will be matched using a RegExp, then I could probably figure out the XPath.  I tend to use this site as my online documentation for XSL: http://www.zvon.org/xxl/XSLTreference/Output/index.html  I'm always interested in finding out what others use.

>> [Project Engineer]
The user wants to see it as two words.  The replace method works well enough since the encoding is consistently aplied by SQL.  I'm using a document approach in another XSLT where I'm trying to produce a whole page (with multiple tabs) using different templates.  That attempt is stalled because I haven't seen a big enough maintainability benefit :(

This current XSLT is rendering a solid 50% faster in IE.  That's w/o figuring out the current problem and relying on the Schema1 name.  That's also without the caching turned on.

I have used EXPLICIT when I don't mind creating data-centic XML and XSLT, but I will only see advantage over what currently exists if I take a generic approach to the problem I'm solving.  The EXPLICIT syntax would be different for each query.
0
 
LVL 9

Author Comment

by:rherguth
ID: 12671175
Uh, OK this turned out to be simple:
<xsl:variable name="datarow" select="."/>

That combined with my AttributeType matching XPath:
<xsl:variable name="fieldvalue" select="$datarow/@*[name() = $fieldname]" default=" "/>

Thanks for your help on the grouping.  That's what I'll be attempting to add next.
0
 
LVL 9

Author Comment

by:rherguth
ID: 12701516
Well it's implemented now and I was able to eliminate some lines of slow ASP javascript, which helps maintainability a bit, but the major improvement was the in user's apparent response time - decreased by 40%, on average.
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12701668
Excellent!

Regards,
Mike Sharp
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question