Generating SQL using XML and XSLT

Question

I have an XML definition that contains an element with child elements. For example:

<a>
 <b>
  <c>C</c>
  <d>D</d>
 </b>
</a>

I have an XSLT with an output of text. For example:

<xsl...>
  <xsl:output method="text" indent="yes"/>
  <xsl:template match="/">
    <xsl:copy-of select="/a/b" />
  ...

I want to copy the entire b element and its children into a whitespace-removed string so that I can generate a SQL query. For example:

select * from some-table where xml = '<b><c>C</c><d>D</d></b>'

At the moment copy-of is finding the b element but dropping off all element and attribute information leaving only the text content within each. I think this might be to do with the output type.

Any ideas?

Solution 1

Here is how it can be done:

<xsl:output method="xml" />

<xsl:template match="/"><xsl:apply-templates select="/a/b" mode="normalize-space" /></xsl:template>

<xsl:template match="text()" mode="normalize-space"><xsl:value-of select="normalize-space(.)" /></xsl:template>
<xsl:template match="@*|node()" mode="normalize-space"><xsl:copy><xsl:apply-templates select="@*|node()" mode="normalize-space" /></xsl:copy></xsl:template>

This method copies nodes, nodes with namespaces and attributes.

Method requires output to be "xml" (not "text" as in original sample). It uses custom template for all TEXT nodes to normalize space inside them (remove leading/trailing whitespace, condense multiple spaces into a single space). Then, it uses simple "identity" template that copies all nodes and their attributes. Both templates use special mode to not to interfere with the rest of XSL.

Unforunately, XSLT processor copies all "unknown" nodes inside xsl:template tag into output document and spaces are one of such nodes. That's why all those templates need to be written in one line with no extra spaces.

PS Although, I agree that searching a normalized XML in RDBMS is kind of weird.

Solution 2

Your SQL statement scares me. XML is case-sensitive, and your comparison there is likely to fail if the input XML and XSLT (including all elements, attributes, and values) aren't cased exactly like that used in the original database insert.

I believe both Oracle (certain) and SQL Server (think so) have mechanisms to do a query against a column containing XML in a more XML-friendly way (for instance, using XPath).

What exactly are you trying to do? Your problem seems deeper than just getting this XSLT to transform correctly.

Solution 3

Maybe a bit too challenging a task for XSLT. The closest I can get is this:

  <xsl:template match="b//*|node()">
    <xsl:copy>
      <xsl:text>&lt;</xsl:text>
      <xsl:value-of select="name()"/>
      <xsl:text>&gt;</xsl:text>
      <xsl:value-of select="text()"/>
      <xsl:apply-templates select="*"/>
      <xsl:text>&lt;/</xsl:text>
      <xsl:value-of select="name()"/>
      <xsl:text>&gt;</xsl:text>
    </xsl:copy>
  </xsl:template>

And is called with:

  <xsl:apply-templates select="/a/b/self::*"/>

This produces the following:

 <b>
  <c>C</c>
  <d>D</d>
 </b>

Where my "solution" falls over is when the elements have attributes. If b had an attribute, the attribute value gets written out. I can't find a way of writing out attributes as and when they;re encountered...

Any ideas?

Comments

[Scott McKenzie]

The point of the question is not the content of the xml or the query, but actually the copying of XML to plain text.

[Mitch Wheat]

decent, inbuilt xml support first appeared in SQL Server 2005

[Scott McKenzie]

Good answer, but I need something with a text output. The bit of XSLT I added got me almost the whole way there but did not deal well with attributes very well. The original was to output XML in SQL. Thanks.