FOR XML Clause in a SQL SELECT statement

You can execute queries that return scanned data and/or fields from the NCS header as XML. You have extensive control on the shape of the XML. Typically, you will be tasked to shape the XML in a way that is elaborately prescribed by a receiving application.

The FOR-clause has the following modes that allow you to render a relational and/or tabular structure into a hierarchical XML structure – elements, attributes, entity encoding and so on:

The data provider throws a “Statement "explicit" not supported (OPL7606)” exception if the EXPLICIT option is exercised.

XML Encoding

This release of data services supports XML 1.0 and UTF-8.

XML Namespaces

To add namespaces to the XML constructed by the FOR XML query, do the following:

Specify the namespace prefix to URI mapping via the WITH XMLNAMESPACES.
Use the namespace prefixes in specifying column aliases.

Example

WITH XMLNAMESPACES (‘uri’ as ns1)

Select

ProductID ‘ns1:ProductID’,

Name ‘ns1:Name’,

Color ‘ns1:Color’

From Product

FOR XML RAW (‘ns1:Prod’), ELEMENTS

This is the XML output from the above query:

<ns1:Prod xmlns:ns1=’uri’>

<ns1:ProductID>316</ns1:ProductID>

<ns1:Name>Blade</ns1:Name>

<ns1:Color>Black</ns1:Color>

</ns1:Prod>

NOTE: XMLDATA and XMLSCHEMA cannot be used WITH XMLNAMESPACES. In this case the schema is indirectly specified using an URI instead of an inline XDR or XSD.

Common Directives

Specify the ROOT option to request a single top level element named <root> or the optionally provided root element name for the resulting XML. Ensure that the root element name is a XML-friendly name. If you fail to do so, data provider encodes the name.

You can optionally specify the TYPE directive to retrieve the results as xml type. The contents of the results are not changed, just the data type. You would do this if you wish to store the data in a SQL Server column. This version of the data provider accepts the TYPE directive but does nothing with it.

Use the BINARY BASE64 option to return data in binary base 64 format. This version of the data provider accepts the BINARY BASE64 directive but does nothing with it.

Element centric form (vs. Attribute centric form)

For RAW and AUTO modes, by default each non-NULL column value in the rowset maps to an attribute of the <row> element. If the ELEMENTS directive is added to the FOR XML clause each column value maps to a sub-element of <row>. You can optionally use XSINIL with ELEMENTS to emit NULL-valued columns as an element with attribute xsi:nil set to true. Path mode is element centric by default but you still need to use XSINIL to emit NULL-valued columns.

Recordset Mode

This mode outputs data in the ADO recordset format. This is typically for use by ASP solutions. It has a root tag, a schema portion and a data portion consisting of one element per row returned by the query. Columns appear as attributes. The schema portion is a legacy XDR that predates the W3C XSD specification.

You can load an ADO Recordset with this data, that is the only important thing about this format.

Syntax: FOR XML RECORDSET

Example: Here is a portion of the output for the query,

select Name,Gender,ROWID from [GPAS #221666 Dual Sided] for xml recordset

- <xml xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:z="#RowsetSchema">

- <s:Schema id="RowsetSchema">

- <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">

- <s:AttributeType name="Name" rs:number="1">

<s:datatype dt:type="string" />

</s:AttributeType>

- <s:AttributeType name="Gender" rs:number="2">

<s:datatype dt:type="string" />

</s:AttributeType>

- <s:AttributeType name="ROWID" rs:number="3">

<s:datatype dt:type="string" />

</s:AttributeType>

</s:ElementType>

</s:Schema>

- <rs:data>

<z:row Name="ALL RIGHT" Gender="M" ROWID="000001" />

<z:row Name="HALF RIGHT" Gender="M" ROWID="000002" />

<z:row Name="ALL WRONG" Gender="-" ROWID="000020" />

</rs:data>

</xml>

Here is code for loading a recordset with the output of the query

Public Function RecordsetFromXMLString(sXML As String) As Recordset

Dim oStream As ADODB.Stream

Set oStream = New ADODB.Stream

oStream.Open

oStream.WriteText sXML 'Give the XML string to the ADO Stream

oStream.Position = 0 'Set the stream position to the start

Dim oRecordset As ADODB.Recordset

Set oRecordset = New ADODB.Recordset

oRecordset.Open oStream 'Open a recordset from the stream

oStream.Close

Set oStream = Nothing

Set RecordsetFromXMLString = oRecordset 'Return the recordset

Set oRecordset = Nothing

End Function

RAW Mode

RAW mode transforms each row in the query result set into an XML element that has the generic identifier <row> or the optionally provided element name. Ensure that the element name is a XML-friendly name. If you fail to do so, data provider encodes the name.

Syntax:

FOR XML RAW [(‘RowElementName’)]

[, BINARY BASE64]

[, TYPE]

[, ROOT [(‘RootElementName’)]]

[, {XMLDATA |XMLSCHEMA [(‘TargetNameSpaceURL’)] }]

[, ELEMENTS [XSINIL | ABSENT]]

FOR XML RAW is treated as FOR XML RAW (‘row’)

FOR XML RAW (‘’) is not legal

FOR XML RAW (‘MyRowElementName’)

It is an error to retrieve binary data without specifying the BINARY BASE64 option.

You can request a schema for the resulting XML. Specify XMLDATA or XMLSCHEMA (with an optional target namespace URI) to have the data provider emit an inline XDR or XSD schema along with the data. Schema appears at the start of the output and each top level element has the schema namespace reference.

AUTO Mode

This returns data as simply nested XML elements. Each table is represented as an XML element named after the table (or its alias). Columns are mapped to attributes or sub-elements.

Syntax

FOR XML AUTO

[, BINARY BASE64]

[, TYPE]

[, ROOT [(‘RootElementName’)]]

[, {XMLDATA |XMLSCHEMA [(‘TargetNameSpaceURL’)] }]

[, ELEMENTS [XSINIL | ABSENT]]

PATH Mode

This mode provides a simple way to mix elements and attributes. It also provides a simple way to provide additional nesting of elements. Specially constructed column names and aliases specify the shape of the data.

Syntax

FOR XML PATH [(‘ElementName’)]

[, BINARY BASE64]

[, TYPE]

[, ROOT [(‘RootElementName’)]]

[, ELEMENTS [XSINIL | ABSENT]]

Column with a name specified as * or node()	column value is inserted; column name is ignored
Column with a name that starts with @	maps to an attribute
Column with a name that has /	indicates a hierarchy; sub-elements are created as needed
Column with a name specified as data()	maps to a list of space delimited values; column name is ignored
Columns with NULL values	maps to the absence of element/attribute unless XSINIL is specified
Column with name specified as comment()	maps to an XML comment
Consecutive columns with the same path prefix	are grouped together

Example of the FOR-clause that uses Path

Assume this simple scenario: we are querying the Department document in a data file. Each output record has an ID column and a Name column. We are tasked to emit each record as below:

<Name>Warehouse</Name>

</Department>

We see one element (Department), one attribute (ID) and one sub-element (Name). The ID column appears as an attribute of the Department element. The Name column appears as a sub-element of the Department element. This output essentially combines the element normal form and attribute normal form. We also note that there is no element that wraps the row output as a whole – there is only one table.

Start with this tabular query:

Select

ID,

Name

From

Department

Modify the query to emit XML as follows:

Select

ID,

Name

From

Department

FOR XML PATH (‘’)

Specify a 0-length string for the PATH argument because we do not want a row wrapper.

Next, specify an alias for the ID column using path style syntax as follows

Department/@ID

/ indicates a hierarchy – ID is a child node of the Department node

@ indicates that the ID column be rendered as an attribute of the Department element

Specify alias for the Name column:

Department/Name

In the above case, we avoid using @ to render the column as a sub-element.

This is the complete Select statement

Select

ID “Department/@ID”,

Name “Department/Name”

From

Department

FOR XML PATH (‘’)

Two records are shown below.

<Name>Sales</Name>

</Department>

<Name>Engineering</Name>

</Department>

Columns with image data

Columns and functions that return image data are treated as NULL columns. No errors result. This applies in particular to the Clips column the Master table, the RICHTIFF and RAWTIFF functions.

If you specify BINARY BASE64 within the For-clause, it is ignored but again, no errors result.

NCS Header Columns

XML queries for these columns are supported. Use the “with NCSHEADER” clause as usual.

Complex (non-string) data types

XML queries on these types are supported: we need to understand how to deal with conversion errors.

Edit Failures Table

XML queries for this table are supported.

Clips Table

XML queries for this table are supported with the caveat that image columns are treated as NULL valued.

Multiple Tables

The data provider does not support multiple tables in the select statement.

Dealing with Xml-Unfriendly Characters

The ScanTools output record may contain the CR and LF characters. Application definition names may contain embedded spaces. Scanned data may contain leading and trailing whitespace. Tag names and values in the XML output can potentially contain invalid characters from an XML parser’s standpoint.

Data services follows W3C and/or SQL Server conventions and guidelines: A “name character” is a letter, digit, the underscore, colon, hyphen, or period character. A “name” starts with a letter, the underscore or colon and is followed by 1 or more name characters. If an identifier (name) used within the application definition contains any other characters, these characters are explicitly escaped by data services when generating tag or attribute names within the xml output. For example the space character is encoded as _x0020_ in the xml output. The pound character (#) appears as _x0023_

This is the process for encoding special characters in the result:

In the query result, the special XML and URL characters in the element and attribute names returned are encoded using the hexadecimal value of the corresponding Unicode character encoded. For example, the element name <Special Chars> is returned as <Special_x0020_Chars>. The attribute name <Col#&2> is returned as <Col_x0023__x0026_2> (both XML and URL special characters are encoded). The _ character itself is escaped only if it is followed by x or X.
If the values of the elements or attribute contain any of the five standard XML character entities (', "", <, >, and &), these special XML characters are always encoded using XML character encoding. For example, the value & in the value of an attribute say <Col1> is encoded as &. However, the # character remains # because it is a valid XML character (not a special XML character).

While defining names for grids, fields, documents, so on in the application definition make sure that they begin with a letter. Alternatively use aliases within the query.

XDR Inline Schema (XMLDATA)

Use this option to generate XML data with an inline schema that is readable by the Microsoft ADO Recordset. Active Server Pages (ASP) applications typically use ADO so this is perfect for such scenarios.

See SQL Server 2005 books online for more information on this.

Caveats: Dataset.ReadXml(XmlReadMode.Auto) can cause undesirable behavior. Use XmlMode.IgnoreSchema instead. Do not use Dataset.ReadXml(XmlReadMode.ReadSchema) with XMLDATA.

XSD Inline Schema (XMLSCHEMA)

Specify this option to have the data provider generate XML output that is compatible with SQL Server 2005 as well as the Dataset component in the .Net framework. This means that the data can be directly put into SQL Server 2005.

See SQL Server (and W3C) documentation for more information on this.

Notes: Dataset.ReadXml(XmlReadMode.ReadSchema) is the preferred way to import the query results into a DataTable.

XMLSCHEMA takes an optional argument, the target namespace URI. Here are examples that clarify how this works:

Examples:

- <Root>

- <xsd:schema … targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" …>

<xsd:import … />

- <xsd:element name="Data">

+ <xsd:complexType>

- <!--

-->

<xsd:attribute name="Application_x0020_Number" type="sqltypes:int" />

<xsd:attribute name="Serial_x0020_Number" type="sqltypes:int" />

<xsd:attribute name="Batch_x0020_Number" type="sqltypes:int" />

<xsd:attribute name="Date_x0020_Scanned" type="sqltypes:date" />

<xsd:attribute name="Document_x0020_Number" type="sqltypes:int" />

- <xsd:attribute name="Edit_x0020_Status">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Edit Status]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Altered_x0020_Since_x0020_Scanned">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Altered Since Scanned]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Grid_x0020_Error_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Grid Error Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="Mark_x0020_Threshold" type="sqltypes:int" />

<xsd:attribute name="Mark_x0020_Discrimination" type="sqltypes:int" />

- <xsd:attribute name="Scanning_x0020_Release_x0020_Level">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Scanning Release Level]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Output_x0020_Stacker">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Output Stacker]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Worst_x0020_Mark_x0020_Intensity">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Worst Mark Intensity]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="Total_x0020_Invalid_x0020_Responses" type="sqltypes:int" />

<xsd:attribute name="Run_x0020_Number" type="sqltypes:int" />

- <xsd:attribute name="Key_x0020_Record_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Key Record Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Record_x0020_Scored_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Record Scored Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Sheet_x0020_Compile_x0020_Edit_x0020_Flags">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Sheet Compile Edit Flags]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Record_x0020_Marked_x0020_Deleted_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Record Marked Deleted Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Record_x0020_Converted_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Record Converted Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="IFD_x0020_Offset" type="sqltypes:int" />

<xsd:attribute name="Count_x0020_Of_x0020_Clips" type="sqltypes:int" />

<xsd:attribute name="Length" type="sqltypes:int" />

<xsd:attribute name="Offset" type="sqltypes:int" />

<xsd:attribute name="Clips" type="sqltypes:binary" />

- <xsd:attribute name="ASCII">

- <xsd:simpleType sqltypes:sqlTypeAlias="[ASCII]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Raw">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Raw]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="ROWID">

- <xsd:simpleType sqltypes:sqlTypeAlias="[ROWID]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

</xsd:complexType>

</xsd:element>

</xsd:schema>

You can specify a target namespace URI as an argument to xmlschema: in the following example, ‘MyData’ exists in the namespace ‘MyUri’. Each row has a default namespace attribute declaration for the specified target namespace URI.

Select * from master for xml raw ('MyData'), root ('MyRoot'),xmlschema ('MyUri')

Partial output appears under:

- <MyRoot>

+ <xsd:schema … targetNamespace="MyUri" …>

<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

- <xsd:element name="MyData">

- <xsd:complexType>

- <!--

-->

<xsd:attribute name="Application_x0020_Number" type="sqltypes:int" />

<xsd:attribute name="Serial_x0020_Number" type="sqltypes:int" />

<xsd:attribute name="Batch_x0020_Number" type="sqltypes:int" />

<xsd:attribute name="Date_x0020_Scanned" type="sqltypes:date" />

<xsd:attribute name="Document_x0020_Number" type="sqltypes:int" />

- <xsd:attribute name="Edit_x0020_Status">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Edit Status]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Altered_x0020_Since_x0020_Scanned">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Altered Since Scanned]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Grid_x0020_Error_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Grid Error Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="Mark_x0020_Threshold" type="sqltypes:int" />

<xsd:attribute name="Mark_x0020_Discrimination" type="sqltypes:int" />

- <xsd:attribute name="Scanning_x0020_Release_x0020_Level">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Scanning Release Level]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Output_x0020_Stacker">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Output Stacker]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Worst_x0020_Mark_x0020_Intensity">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Worst Mark Intensity]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="Total_x0020_Invalid_x0020_Responses" type="sqltypes:int" />

<xsd:attribute name="Run_x0020_Number" type="sqltypes:int" />

- <xsd:attribute name="Key_x0020_Record_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Key Record Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Record_x0020_Scored_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Record Scored Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Sheet_x0020_Compile_x0020_Edit_x0020_Flags">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Sheet Compile Edit Flags]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Record_x0020_Marked_x0020_Deleted_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Record Marked Deleted Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Record_x0020_Converted_x0020_Flag">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Record Converted Flag]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="IFD_x0020_Offset" type="sqltypes:int" />

<xsd:attribute name="Count_x0020_Of_x0020_Clips" type="sqltypes:int" />

<xsd:attribute name="Length" type="sqltypes:int" />

<xsd:attribute name="Offset" type="sqltypes:int" />

<xsd:attribute name="Clips" type="sqltypes:binary" />

- <xsd:attribute name="ASCII">

- <xsd:simpleType sqltypes:sqlTypeAlias="[ASCII]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="Raw">

- <xsd:simpleType sqltypes:sqlTypeAlias="[Raw]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

- <xsd:attribute name="ROWID">

- <xsd:simpleType sqltypes:sqlTypeAlias="[ROWID]">

<xsd:restriction base="sqltypes:nchar" />

</xsd:simpleType>

</xsd:attribute>

</xsd:complexType>

</xsd:element>

</xsd:schema>