Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Server Administration Guide > Using XML as a Data Source > Using the Siebel Analytics Server XML Gateway >

Siebel Analytics Server XML Gateway Example

---

The following sample XML data document (mytest.xml) references an XML schema contained in an external file. The schema file is shown following the data document. The generated XML schema information available for import to the repository is shown at the end.

<?xml version="1.0"?>
<test xmlns="x-schema:mytest_sch.xml">

<row>
<p1>0</p1>
<p2 width="5">
      <p3>hi</p3>
      <p4>
         <p6>xx0</p6>
         <p7>yy0</p7>
      </p4>
      <p5>zz0</p5>
</p2>
</row>

<row>
<p1>1</p1>
<p2 width="6">
      <p3>how are you</p3>
      <p4>
         <p6>xx1</p6>
         <p7>yy1</p7>
      </p4>
      <p5>zz1</p5>
</p2>
</row>

<row>
<p1>a</p1>
<p2 width="7">
      <p3>hi</p3>
      <p4>
         <p6>xx2</p6>
         <p7>yy2</p7>
      </p4>
      <p5>zz2</p5>
</p2>
</row>

<row>
<p1>b</p1>
<p2 width="8">
      <p3>how are they</p3>
      <p4>
         <p6>xx3</p6>
         <p7>yy3</p7>
      </p4>
      <p5>zz2</p5>
</p2>
</row>
</test>

The corresponding schema file follows:

<Schema xmlns="urn:schemas-microsoft-com:xml-data"
      xmlns:dt="urn:schemas-microsoft-com:datatypes">
      <ElementType name="test" content="eltOnly" order="many">
         <element type="row"/>
      </ElementType>
      <ElementType name="row" content="eltOnly" order="many">
      <element type="p1"/>
         <element type="p2"/>
      </ElementType>
      <ElementType name="p2" content="eltOnly" order="many">
         <AttributeType name="width" dt:type="int" />
         <attribute type="width" />
         <element type="p3"/>
         <element type="p4"/>
         <element type="p5"/>
      </ElementType>
      <ElementType name="p4" content="eltOnly" order="many">
         <element type="p6"/>
         <element type="p7"/>
      </ElementType>
      <ElementType name="p1" content="textOnly" dt:type="string"/>
      <ElementType name="p3" content="textOnly" dt:type="string"/>
      <ElementType name="p5" content="textOnly" dt:type="string"/>
      <ElementType name="p6" content="textOnly" dt:type="string"/>
      <ElementType name="p7" content="textOnly" dt:type="string"/>
</Schema>

The name of the table generated from the preceding XML data document (mytest.xml) would be mytest and the column names would be p1, p3, p6, p7, p5, and width.

In addition, to preserve the context in which each column occurs in the document and to distinguish between columns derived from XML elements with identical names but appearing in different contexts, a list of fully qualified column names is generated, based on the XPath proposal of the World Wide Web Consortium, as follows:

//test/row/p1
//test/row/p2/p3
//test/row/p2/p4/p6
//test/row/p2/p4/p7
//test/row/p2/p5
//test/row/p2@width

The following example is a more complex example that demonstrates the use of nested table structures in an XML document. Note that you may optionally omit references to an external schema file, in which case all elements would be treated as being of the Varchar character type.

===Invoice.xml===
<INVOICE>
   <CUSTOMER>
      <CUST_ID>1</CUST_ID>
      <FIRST_NAME>Nancy</FIRST_NAME>
      <LAST_NAME>Fuller</LAST_NAME>
      <ADDRESS>
         <ADD1>507 - 20th Ave. E.,</ADD1>
         <ADD2>Apt. 2A</ADD2>
         <CITY>Seattle</CITY>
         <STATE>WA</STATE>
         <ZIP>98122</ZIP>
      </ADDRESS>
      <PRODUCTS>
          <CATEGORY>
             <CATEGORY_ID>CAT1</CATEGORY_ID>
             <CATEGORY_NAME>NAME1</CATEGORY_NAME>
             <ITEMS>    
                  <ITEM>   
                     <ITEM_ID>1</ITEM_ID>
                     <NAME></NAME>
                     <PRICE>0.50</PRICE>
                     <QTY>2000</QTY>
                  </ITEM>   
                  <ITEM>   
                     <ITEM_ID>2</ITEM_ID>
                     <NAME>SPRITE</NAME>
                     <PRICE>0.30</PRICE>
                     <QTY></QTY>
                  </ITEM>   
             </ITEMS>    
         </CATEGORY>
          <CATEGORY>
             <CATEGORY_ID>CAT2</CATEGORY_ID>
             <CATEGORY_NAME>NAME2</CATEGORY_NAME>
             <ITEMS>    
                  <ITEM>   
                     <ITEM_ID>11</ITEM_ID>
                     <NAME>ACOKE</NAME>
                     <PRICE>1.50</PRICE>
                     <QTY>3000</QTY>
                  </ITEM>   
                  <ITEM>   
                     <ITEM_ID>12</ITEM_ID>
                     <NAME>SOME SPRITE</NAME>
                     <PRICE>3.30</PRICE>
                     <QTY>2000</QTY>
                  </ITEM>   
             </ITEMS>    
         </CATEGORY>
      </PRODUCTS>
   </CUSTOMER>
   <CUSTOMER>
      <CUST_ID>2</CUST_ID>
      <FIRST_NAME>Andrew</FIRST_NAME>
      <LAST_NAME>Carnegie</LAST_NAME>
      <ADDRESS>
         <ADD1>2955 Campus Dr.</ADD1>
         <ADD2>Ste. 300</ADD2>
         <CITY>San Mateo</CITY>
         <STATE>CA</STATE>
         <ZIP>94403</ZIP>
      </ADDRESS>
      <PRODUCTS>
          <CATEGORY>
             <CATEGORY_ID>CAT22</CATEGORY_ID>
             <CATEGORY_NAME>NAMEA1</CATEGORY_NAME>
             <ITEMS>    
                  <ITEM>   
                     <ITEM_ID>122</ITEM_ID>
                     <NAME>DDDCOKE</NAME>
                     <PRICE>11.50</PRICE>
                     <QTY>2</QTY>
                  </ITEM>   
                  <ITEM>   
                     <ITEM_ID>22</ITEM_ID>
                     <NAME>PSPRITE</NAME>
                     <PRICE>9.30</PRICE>
                     <QTY>1978</QTY>
                  </ITEM>   
             </ITEMS>    
         </CATEGORY>
          <CATEGORY>
             <CATEGORY_ID>CAT24</CATEGORY_ID>
             <CATEGORY_NAME>NAMEA2</CATEGORY_NAME>
             <ITEMS>    
                  <ITEM>   
                     <ITEM_ID>19</ITEM_ID>
                     <NAME>SOME COKE</NAME>
                     <PRICE>1.58</PRICE>
                     <QTY>3</QTY>
                  </ITEM>   
                  <ITEM>   
                     <ITEM_ID>15</ITEM_ID>
                     <NAME>DIET SPRITE</NAME>
                     <PRICE>9.30</PRICE>
                     <QTY>12000</QTY>
                  </ITEM>   
             </ITEMS>    
         </CATEGORY>
      </PRODUCTS>
   </CUSTOMER>
   <CUSTOMER>
      <CUST_ID>3</CUST_ID>
      <FIRST_NAME>Margaret</FIRST_NAME>
      <LAST_NAME>Leverling</LAST_NAME>
      <ADDRESS>
         <ADD1>722 Moss Bay Blvd.</ADD1>
         <ADD2> </ADD2>
         <CITY>Kirkland</CITY>
         <STATE>WA</STATE>
         <ZIP>98033</ZIP>
      </ADDRESS>
      <PRODUCTS>
          <CATEGORY>
             <CATEGORY_ID>CAT31</CATEGORY_ID>
             <CATEGORY_NAME>NAMEA3</CATEGORY_NAME>
             <ITEMS>    
                  <ITEM>   
                     <ITEM_ID>13</ITEM_ID>
                     <NAME>COKE33</NAME>
                     <PRICE>30.50</PRICE>
                     <QTY>20033</QTY>
                  </ITEM>   
                  <ITEM>   
                     <ITEM_ID>23</ITEM_ID>
                     <NAME>SPRITE33</NAME>
                     <PRICE>0.38</PRICE>
                     <QTY>20099</QTY>
                  </ITEM>   
             </ITEMS>    
         </CATEGORY>
          <CATEGORY>
             <CATEGORY_ID>CAT288</CATEGORY_ID>
             <CATEGORY_NAME>NAME H</CATEGORY_NAME>
             <ITEMS>    
                  <ITEM>   
                     <ITEM_ID>19</ITEM_ID>
                     <NAME>COLA</NAME>
                     <PRICE>1.0</PRICE>
                     <QTY>3</QTY>
                  </ITEM>   
                  <ITEM>   
                     <ITEM_ID>18</ITEM_ID>
                     <NAME>MY SPRITE</NAME>
                     <PRICE>8.30</PRICE>
                     <QTY>123</QTY>
                  </ITEM>   
             </ITEMS>    
         </CATEGORY>
      </PRODUCTS>
   </CUSTOMER>
</INVOICE>

The generated XML schema shown next consists of one table (INVOICE) with the following column names and their corresponding fully qualified names.

Column	Fully Qualified Name
ADD1	//INVOICE/CUSTOMER/ADDRESS/ADD1
ADD2	//INVOICE/CUSTOMER/ADDRESS/ADD2
CITY	//INVOICE/CUSTOMER/ADDRESS/CITY
STATE	//INVOICE/CUSTOMER/ADDRESS/STATE
ZIP	//INVOICE/CUSTOMER/ADDRESS/ZIP
CUST_ID	//INVOICE/CUSTOMER/CUST_ID
FIRST_NAME	//INVOICE/CUSTOMER/FIRST_NAME
LAST_NAME	//INVOICE/CUSTOMER/LAST_NAME
CATEGORY_ID	//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/CATEGORY_ID
CATEGORY_NAME	//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/CATEGORY_NAME
ITEM_ID	//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/ITEM_ID
NAME	//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/NAME
PRICE	//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/PRICE
QTY	//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/QTY

Only tags with values are extracted as columns. An XML query generates fully qualified tag names, which helps to make sure that appropriate columns are retrieved.

These are the results of a sample query against the INVOICE table.

select first_name, last_name, price, qty, name from invoice
------------------------------------------------------------
FIRST_NAME   LAST_NAME         PRICE   QTY   NAME
------------------------------------------------------------
Andrew      Carnegie           1.58     3    SOME COKE
Andrew      Carnegie          11.50     2    DDDCOKE
Andrew      Carnegie           9.30   12000  DIET SPRITE
Andrew      Carnegie           9.30    1978  PSPRITE
Margar      Leverling          0.38   20099  SPRITE33
Margar      Leverling          1.0      3    COLA
Margar      Leverling         30.50   20033  COKE33
Margar      Leverling          8.30    123   MY SPRITE
Nancy       Fuller             0.30          SPRITE
Nancy       Fuller             0.50    2000
Nancy       Fuller             1.50    3000  ACOKE
Nancy       Fuller             3.30    2000  SOME SPRITE
------------------------------------------------------------
Row count: 12

Bookshelf Home | Contents | Index | Search | PDF