17.10 - Canonical XML Publishing - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - XML Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1140-171K
Language
English (United States)

The canonical XML representation of SQL query results takes the form of a document element named QuerySchema which has 0 or more child elements named Row. Each Row element corresponds to a row in the SQL query result set. The Row elements have children elements corresponding to the projection list of the SQL query. For example, consider an SQL query such as the following:

SELECT CUSTDTL.ID AS CustomerID, CUSTDTL.NAME, CUSTDTL.ADDRESS, CUSTDTL.PHONE1,
   CUSTDTL.PHONE2, CUSTDTL.FAX, CUSTDTL.EMAIL, ORDERDTL.ORDER_ID AS OrderNumber,
   ORDERDTL.ORDER_DATE AS OrderDate, ORDERDTL.CONTACT AS OrderContact,    ORDERDTL.CONTACT_PHONE AS OrderPhone, ORDERDTL.SHIP_TO_ADDRESS AS OrderShipTo,
   ORDERDTL.SUB_TOTAL AS OrderSubTotal, ORDERDTL.TAX AS OrderTax, ORDERDTL.TOTAL AS
   OrderTotal, LINEITEMDTL.ITEM_ID AS ItemID, LINEITEMDTL.QUANTITY AS ItemQuantity,
   LINEITEMDTL.PART_NUMBER AS ItemPartNumber, LINEITEMDTL.DESCRIPTION AS ItemDescription,
   LINEITEMDTL.UNIT_PRICE AS ItemUnitPrice, LINEITEMDTL.PRICE AS ItemPrice
FROM CUSTDTL, ORDERDTL, LINEITEMDTL
WHERE CUSTDTL.ID=ORDERDTL.CUSTOMER_ID AND ORDERDTL.ORDER_ID=LINEITEMDTL.ORDER_ID;

The CUSTDTL, ORDERDTL, and LINEITEMDTL tables reflect the purchase order history data.