Examples: Using CREATEXML to Create XML Type Instances

Teradata Vantageā„¢ XML Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1140-162K

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