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?

Answer 1

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.