17.10 - Examples: Using CREATEXML to Create XML Type Instances - 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)

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