Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Server Administration Guide > Using XML as a Data Source for Analytics > 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@widthThe 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.
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 |
Siebel Analytics Server Administration Guide Published: 11 March 2004 |