Siebel Business Analytics Server Administration Guide > Using XML as a Data Source for Siebel Business Analytics > Using the Analytics Server XML Gateway >
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.
|
|
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, to help 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
|