17.05 - Example: SHOW VIEW in XML Format - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

This example returns the CREATE VIEW text for table user_name.v1 in XML format. Unlike a SHOW IN XML TABLE request, the XML document generated by a SHOW IN XML VIEW request does not report the parsed query for the view, and only contains the column definitions of the view and the database objects it references. As a result, this XML document cannot be used to recreate the view. However, it can be used to create a table having the same column definition of the original view.

The table that v1 is defined on looks like this.

     CREATE SET TABLE TESTDB.vt1, NO FALLBACK, NO BEFORE JOURNAL,
         NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO (
       a1 INTEGER NOT NULL,
       b1 INTEGER,
       c1 CHARACTER(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
       d1 DECIMAL(15,2));

The create text for v1 looks like this.

     CREATE VIEW v1 AS
       SELECT a1, b1, c1
       FROM vt1
       WHERE d1 > 10000;

When you submit the following SHOW VIEW IN XML request, the database returns the report that follows the request.

     SHOW VIEW v1 IN XML;
<?xml version="1.0" encoding="utf-8"?>
<TeradataDBObjectSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.teradata.com/TeradataDBObject
TeradataDBObject.xsd"
xmlns="http://schemas.teradata.com/TeradataDBObject" version="1.0">
<View name="v1" dbName="testdb">
<ColumnList>
<Column name="a1" order="1">
<DataType><Integer/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="b1" order="2">
<DataType><Integer/></DataType>
</Column>
<Column name="c1" order="3">
<DataType>
<Char length="20" varying="false" uppercase="false" casespecific="false"
charset="unicode"/>
</DataType>
</Column>        
</ColumnList>
<RefList>
<Ref name="vt1" dbName="testdb" type="table"/>
</RefList>
<SQLText>
<![CDATA[
CREATE VIEW V1 AS
SELECT A1, B1, C1
FROM VT1
WHERE D1 > 10000;
</SQLText>
</View>
<Environment>
<Server dbRelease="14.10.00.00" dbVersion="14.10.00.06" hostName="testhost"/>
<User userId="0" userName="dbc"/>
<Session charset="utf8" dateTime="2012-01-01T14:00:00-08:00"/>
</Environment>
</TeradataDBObjectSet>