XML Publishing Using SQL › XML Publishing › Mapping Plain Text SQL to XML › Mapping SQL Data Type Values to XML Schema Data Type Values

Mapping SQL Data Type Values to XML Schema Data Type Values

This feature supports mapping SQL data type values to XML schema data type values; however, mapping of GRAPHIC and VARGRAPHIC are not supported.

You can map null values using absence or xsi:nil="true".

The complete mapping rules are described in the SQL/XML ISO standard specification. As an oversimplification, mapping can be described as the result of the casting of the SQL data value to VARCHAR(max).

The following table shows some of the character value mappings:

This mapping does not apply to the characters belonging to an XML CDATA section; a CDATA section begins with the string "<![CDATA[" and ends with the string "]]>".

Example

In the following example, the use of many of the SQL/XML functions is shown. The result of the SELECT is an XML document that contains all the employees from the DEMOEMPL.EMPLOYEE table, grouped by department. The DEMOEMPL.EMPLOYEE and DEMOEMPL.DEPARTMENT tables are equi-joined on the DEPT_ID. To limit the size of the output, a WHERE clause is coded for the DEPT_ID column. Note how the SELECT statement clearly and naturally reflects the structure of the XML document.

select
  XMLSERIALIZE(CONTENT
    XMLCONCAT(
      XMLPI(NAME "xml"
       ,'version="1.0" encoding="UTF-8" standalone="yes"')
     ,XMLELEMENT(NAME "EmployeesByDepartment"
       ,XMLAGG
          XMLELEMENT(NAME "Department"
           ,XMLATTRIBUTES(DEPT_ID as "DeptId"
                         ,DEPT_NAME as "DeptName")
           ,select XMLAGG(
              XMLELEMENT(NAME "Employee"
               ,XMLATTRIBUTES(EMP_ID as "EmpId")
               ,e.EMP_FNAME
               ,e.EMP_LNAME
               ,XMLELEMENT(Name "Address"
                 ,XMLFOREST(
                    e.STREET as "Street"
                   ,e.CITY as "City"
                   ,e.STATE as "State"
                  )
                )
              )
            )
            from DEMOEMPL.EMPLOYEE e
            where d.DEPT_ID = e.DEPT_ID
           )
         )
       )
     )
   as VARCHAR(5000)
   )
 from DEMOEMPL.DEPARTMENT d
 where d.DEPT_ID < 1120

The result, which has been formatted for clarity, is similar to the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<EmployeesByDdpartment>
  <Department DeptId="1100" DeptName="PURCHASING - USED CARS">
    <Employee EmpId="5008">
      Timothy Fordman
      <Address>
       <Street>60 Boston Rd</Street>
       <City>Brookline</City>
       <State>MA</State>
      </Address>
    </Employee>
    <Employee Empid="4703">
      Martin Halloran
      <Address>
        <Street>27 Elm St</Street>
        <City>Brookline</City>
        <State>MA</State>
      </Address>
    </Employee>
    <Employee EmpId="2246">
      Marylou Hamel
      <Address>
        <Street>11 Main St</Street>
        <City>Medford</City>
        <State>MA</State>
      </Address>
    </Employee>
</Department>
<Department DeptId="1110" DeptName="PURCHASING - NEW CARS">
    <Employee EmpId="2106">
      Susan Widman
      <Address>
       <Street>43 Oak St</Street>
       <City>Medford</City>
       <State>MA</State>
      </Address>
    <Employee EmpId="1765">
      David Alexander
      <Address>
       <Street>18 Cross St</Street>
       <City>Grover</City>
       <State>MA</State>
      </Address>
    </Employee>
</Department>
</EmployeesByDepartment>