16.20 - Examples: Using CREATEXML to Create XML Type Instances - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ XML Data Type

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

In the following query, an XML type instance is created from its text representation stored in the customerXMLText column. This column can be a VARCHAR or CLOB type.

SELECT customerID, CREATEXML(customerXMLText)
FROM customerText;

Here are partial results from the query. The ellipsis (...) is not part of the query results. It indicates that the query returns additional results that are truncated in the example.

 customerID CREATEXML(customerXMLText)
----------- -----------------------------------------------------------
          1 <?xml version="1.0" encoding="UTF-8" ?> <Customer>  <Name>John Hancock</Name>  <Address>100 1st Street, San Francisco, CA 94118</Address>  <Phone1>(858)555-1234</Phone1>  <Phone2>(858)555- ...

Once an XML type instance is created, you can invoke various XML processing operations on that instance.

SELECT customerID, CREATEXML(customerXMLText).XMLEXTRACT('/Customer/Address', NULL)
FROM customerText;

Query result:

customerID  CREATEXML(customerXMLText).XMLEXTRACT('/Customer/Address', NULL)
----------- ----------------------------------------------------------------
         1  <Address>100 1st Street, San Francisco, CA 94118</Address>
SELECT customerID, CREATEXML(customerXMLText).ISSCHEMAVALID(schematab.schemaContent,
'Customer', NULL)
FROM customerText, schematab
WHERE schematab.schemaid = 'customerschema.xsd';

Query result:

customerID CREATEXML(customerXMLText).ISSCHEMAVALID(schemaContent, 'Customer', NULL)
---------- ------------------------------------------------------------------------
         1                                                                        1