17.10 - Examples: Using CREATEXML to Create XML Type Instances - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - XML Data Type

Advanced SQL Engine
Teradata Database
Release Number
July 2021
Last Update
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

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