Sources > XML

XML files may have complex structure and contains objects of different types. To select only necessary objects use XPath query passed in first parameter. When no parameters are passed, query will iterate children nodes of root nod. Example: if we have XML file cust.xml with the following structure

<Root>
  <Customers>
    <Customer CustomerID="GREAL">
      <CompanyName>Great Lakes Food Market</CompanyName>
      <ContactName>Howard Snyder</ContactName>
      <ContactTitle>Marketing Manager</ContactTitle>
      <Phone>(503) 555-7555</Phone>
      <FullAddress>
        <Address>2732 Baker Blvd.</Address>
        <City>Eugene</City>
        <Region>OR</Region>
        <PostalCode>97403</PostalCode>
        <Country>USA</Country>
      </FullAddress>
    </Customer>
  </Customers>
  <Orders>
    <Order>
      <CustomerID>GREAL</CustomerID>
      <EmployeeID>6</EmployeeID>
      <OrderDate>1997-05-06T00:00:00</OrderDate>
      <RequiredDate>1997-05-20T00:00:00</RequiredDate>
      <ShipInfo ShippedDate="1997-05-09T00:00:00">
        <ShipVia>2</ShipVia>
        <Freight>3.35</Freight>
        <ShipName>Great Lakes Food Market</ShipName>
        <ShipAddress>2732 Baker Blvd.</ShipAddress>
        <ShipCity>Eugene</ShipCity>
        <ShipRegion>OR</ShipRegion>
        <ShipPostalCode>97403</ShipPostalCode>
        <ShipCountry>USA</ShipCountry>
      </ShipInfo>
    </Order>
  </Orders>
</Root>

we can select only customers:

select * from cust('//Customer')

or only orders:

select * from cust('//Order')

Columns are mapped to node attributes. To get node text or reference nested nodes use XPath expession as column name. Example:

select "/CompanyName", "/FullAddress/City" from cust('//Customer')

To refer to current node use "/current", parent node - "/.."

Joined customers and orders:

select o."/OrderDate", c."/CompanyName", c."/FullAddress/City"

from cust('//Customer') c join cust('//Order') o on o."/CustomerID" = c.CustomerID