xmlQuery statement

The xmlQuery statement lets you retrieve XML content. You can use multiple xmlQuery statements within a query and therefore aggregate data from disparate sources. The resulting data from xmlQuery statements can be further manipulated using a sqlQuery statement.

    <xmlQuery tableName="">
       <from type=""></from>
       <path type=""></path>
       <fields></fields>
    </xmlQuery>

xmlQuery attributes

tableName

Let’s you attribute a unique name to the resulting data. The resulting data can be further manipulated using a sqlQuery statement.

xmlQuery elements

from

Lets you specify how the XML data is accessed. The value of the element depends on the value of the type attribute.

type	Description
Url	The XML content is accessed from a URL. The value of the from element must specify the URL for retrieving XML data.
Inline	The XML content is embedded directly in the from element.

You can use a parameter to specify both a URL and XML data within the from element.

The following statement will return the data directly embedded in the from element.

Sample

    <xmlQuery tableName="Test">
       <from type="Inline">
           <images>
               <image>
                   <ID>1</ID>
                   <album>Family</album>
                   <path>/family/us.jpg</path>
                   <description>here goes the description</description>
                   <date>2006-11-20 10:20:00</date>
               </image>
               <image>
                   <ID>2</ID>
                   <album>Work</album>
                   <path>/work/coleagues.jpg</path>
                   <description>here goes the description</description>
                   <date>2006-11-21 12:34:00</date>
               </image>
           </images>
        </from>
      <path type="XPath"></path>
    </xmlQuery>

Lets you specify the expression used to select a subset of the XML data. The value of the element depends on the value of the type attribute.

type	Description
XPath	An XPath expression is used to select a subset of the XML data.
ElementPath	A custom ElementPath expression is used.

Additional information when using XPath formula

The xmlQuery statement relies on Dataset ReadXml method to parse XML data. You can therefore provide an in-line XML schema to specify the table definition as shown in the following example:

Sample

    <xmlQuery tableName="Test">
        <from type="Inline">
            <![CDATA[
              <NewDataSet>
                <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
                    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="CompleteList" msdata:UseCurrentLocale="true">
                        <xs:complexType>
                            <xs:choice minOccurs="0" maxOccurs="unbounded">
                                <xs:element name="CompleteList">
                                    <xs:complexType>
                                        <xs:sequence>
                                            <xs:element name="Disp-CalculatedColumnText" type="xs:string" minOccurs="0" />
                                             <xs:element name="Disp-CalculatedColumnNumber" type="xs:double" minOccurs="0" />
                                            <xs:element name="Disp-CalculatedColumnDateTime" type="xs:dateTime" minOccurs="0" />
                                        </xs:sequence>
                                    </xs:complexType>
                                </xs:element>
                            </xs:choice>
                        </xs:complexType>
                    </xs:element>
                </xs:schema>
                <CompleteList>
                    <Disp-CalculatedColumnText>line1</Disp-CalculatedColumnText>
                    <Disp-CalculatedColumnNumber>1</Disp-CalculatedColumnNumber>
                    <Disp-CalculatedColumnDateTime>2007-08-07T07:35:00+02:00</Disp-CalculatedColumnDateTime>
                </CompleteList>
                <CompleteList>
                    <Disp-CalculatedColumnText>line3</Disp-CalculatedColumnText>
                    <Disp-CalculatedColumnNumber>3</Disp-CalculatedColumnNumber>
                    <Disp-CalculatedColumnDateTime>2007-08-07T00:00:00+02:00</Disp-CalculatedColumnDateTime>
                </CompleteList>
            </NewDataSet>
                 ]]>
        </from>
        <path type="XPath"></path>
    </xmlQuery>

If no schema is specified, the structure is inferred from the XML data according to the rule of the DataSet.ReadXml method.

When you specify an XPath expression through the path element, three cases have to be considered:

The expression does not return any element. An exception is thrown.
The expression returns a single element. This element will be considered the root element of the XML data to be parsed.
The expression returns multiple elements. The resulting elements will be assembled together to form the XML data to be parsed.

The following sample query will retrieve XML data from http://feeds2.feedburner.com/CodeBetter RSS feed. The XPath expression specified in path will filter the XML data returned by the specified URL and only keep “item” elements under any “channel” element. Additionally, we are using a sqlQuery statement to keep only those items (articles) that have the word “repository” in their title.

Sample

    <root>
       <xmlQuery tableName="CodeBetterRss">
           <from type="Url">
               <![CDATA[http://feeds2.feedburner.com/CodeBetter]]>
           </from>
           <path type="XPath">//channel/item</path>
       </xmlQuery>

       <sqlQuery tableName="FinalResult">
           SELECT * from CodeBetterRss
           WHERE title LIKE "%repository%"
       </sqlQuery>
    </root>