Edit

Creating CDATA Sections Using sql:use-cdata (SQLXML 4.0)

  • Article

Applies to: SQL Server Azure SQL Database

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

A database in Microsoft SQL Server can sometimes contain characters that are treated as markup characters by the XML parser; for example, angle brackets (< and >), the less-than-or-equal-to symbol (<=), and the ampersand (&) are treated as markup characters. However, you can wrap this type of special characters in a CDATA section to prevent them from being treated as markup characters. The text within the CDATA section is treated by the XML parser as plain text.

The sql:use-cdata annotation is used to specify that the data returned by SQL Server should be wrapped in a CDATA section (that is, it indicates whether the value from a column that is specified by sql:field should be enclosed in a CDATA section). The sql:use-cdata annotation can be specified only on elements that map to a database column.

The sql:use-cdata annotation takes a Boolean value (0 = false, 1 = true). The acceptable values are 0, 1, true, and false.

This annotation cannot be used with sql:url-encode or on the ID, IDREF, IDREFS, NMTOKEN, and NMTOKENS attribute types.

Examples

To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specifying sql:use-cdata on an element

In the following schema, sql:use-cdata is set to 1 (True) for the <AddressLine1> within the <Address> element. As a result, the data is returned in a CDATA section.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:element name="Address"   
               sql:relation="Person.Address"   
               sql:key-fields="AddressID" >  
   <xsd:complexType>  
        <xsd:sequence>  
          <xsd:element name="AddressID"  type="xsd:string" />  
          <xsd:element name="AddressLine1" type="xsd:string"   
                       sql:use-cdata="1" />  
        </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>
To test a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as UseCData.xml.

  2. Copy the following template and paste it into a text file. Save the file as UseCDataT.xml in the same directory where you saved UseCData.xml.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
    <sql:xpath-query mapping-schema="UseCData.xml">  
        /Address[AddressID < 11]  
    </sql:xpath-query>  
</ROOT>

    The directory path specified for the mapping schema (UseCData.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\SqlXmlTest\UseCData.xml"

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

This is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">   
  <Address>   
    <AddressID>1</CustomerID>   
    <AddressLine1>   
      <![CDATA[ 1970 Napa Ct.  ]]>   
    </AddressLine1>   
  </Address>  
  <Address>  
    <AddressLine1>   
      <![CDATA[ 9833 Mt. Dias Blv. ]]>   
    </AddressLine1>   
  </Address>  
  ...  
</ROOT>