Export (0) Print
Expand All

Creating CDATA Sections Using sql:use-cdata

SQL Server 2000

In XML, CDATA sections are used to escape blocks of text containing characters that would otherwise be recognized as markup.

Microsoft® SQL Server™ data may contain characters that are considered special by the XML parser, for example, characters such as <, >, <=, & are treated as markup characters. If you want to avoid SQL Server data containing special characters being treated as markup, you can wrap them in a CDATA section. The text placed in the CDATA section is treated as plain text.

The sql:use-cdata annotation is used specify if the data returned by SQL Server be wrapped in a CDATA section. Use sql:use-cdata annotation to indicate if the value from the column specified by sql:field should be enclosed in a CDATA section. The sql:use-cdata annotation can be specified on <ElementType> or <element>, and takes a Boolean value (0 = FALSE, 1 = TRUE). sql:use-cdata cannot be used with sql:url-encode or on any of the attribute types ID, IDREF, IDREFS, NMTOKEN, or NMTOKENS.

Examples

To create working samples using these examples, you must create the nwind virtual directory (to access the Northwind database) and a virtual name of template type. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

In creating working samples in each example, templates are used to specify XPath queries against the mapping XDR schema. There are different ways of using annotated XDR schemas in queries, for example, inline schemas and schemas in the URL. For more information, see Using Annotated XDR Schemas in Queries.

A. Specify sql:use-cdata on an element

In this schema, sql:use-cdata is set to 1 (TRUE) for the <ProductName> element. As a result, the data for <ProductName> is returned in the CDATA section.

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
   xmlns:dt="urn:schemas-microsoft-com:datatypes"
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <ElementType name="ProductName" content="textOnly" />
   <ElementType name="Products" >
      <element type="ProductName" sql:use-cdata="1"  />
   </ElementType>
</Schema>

Testing a sample XPath query against the schema

  1. Save the schema (MySchema.xml) in the directory associated with the template virtual name that you have already created (or one of its subdirectories, in which case you must specify the relative path in the mapping-schema attribute).

  2. Create this template (MySchemaT.xml) and save it in the directory associated with the template virtual name:
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:xpath-query mapping-schema="MySchema.xml">
        /Products
      </sql:xpath-query>
    </ROOT>
    
  3. This URL executes the template:
    http://IISServer/VirtualRoot/template/MySchemaT.xml
    

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Products> 
    <ProductName> 
      <![CDATA[Alice Mutton]]> 
    </ProductName> 
  </Products> 
</ROOT>

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP

Show:
© 2014 Microsoft