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>