XML UDFs

These UDFs support the XML data type. The XML content is stored in compact binary form or CLOBs that preserve the information set of the XML document. These UDFs have been introduced to support the XML files that can be loaded to or extracted from the Teradata Database tables. Depending on the data element chosen, the data is either tokenized or encrypted.

pty_xmlins

This UDF protects the XML value using type-preserving data elements, such as, token and No Encryption for access control.

Signature:

pty_xmlins(col XML, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Name	Type	Description
`col`	XML	Specifies the XML data to protect.
`dataelement`	VARCHAR	Specifies the name of the data element.
`resultlen`	INTEGER	Specifies the length of the buffer to hold the result.
`communicationid`	INTEGER	Specify the value as 0. This parameter is deprecated.
`scid`	INTEGER	Specify the value as 0. This parameter is deprecated.

Returns:
The function returns the protected CLOB value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Caution: Tokenizing XML data with Printable tokenization does not return a valid XML format output.

Example:

select pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0) "Protected Data";

pty_xmlsel

This UDF unprotects the protected CLOB value.

Signature:

pty_xmlsel(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Name	Type	Description
`col`	CLOB	Specifies the CLOB data to unprotect.
`dataelement`	VARCHAR	Specifies the name of the data element.
`resultlen`	INTEGER	Specifies the length of the buffer to hold the result.
`communicationid`	INTEGER	Specify the value as 0. This parameter is deprecated.
`scid`	INTEGER	Specify the value as 0. This parameter is deprecated.

Returns:

The function returns the unprotected XML values.
The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

sel
pty_xmlsel( 
pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0),'TE_A_N_S23_L2R2_Y',1500,0,0) "UnProtected Data";

pty_xmlselex

This UDF unprotects the protected CLOB value with strong encryption.

Signature:

pty_xmlselex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Name	Type	Description
`col`	CLOB	Specifies the CLOB data to unprotect.
`dataelement`	VARCHAR	Specifies the name of the data element.
`resultlen`	INTEGER	Specifies the length of the buffer to hold the result.
`communicationid`	INTEGER	Specify the value as 0. This parameter is deprecated.
`scid`	INTEGER	Specify the value as 0. This parameter is deprecated.

Returns:

The function returns the unprotected XML values.
The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

sel
pty_xmlselex( 
pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0),'TE_A_N_S23_L2R2_Y',1500,0,0) "UnProtected Data";

pty_xmlenc

This UDF protects the XML data using an Encryption data element.

Signature:

pty_xmlenc(col XML, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Name	Type	Description
`col`	XML	Specifies the XML data to protect.
dataelemen	VARCHAR	Specifies the name of the data element.
`resultlen`	INTEGER	Specifies the length of the buffer to hold the result.
`communicationid`	INTEGER	Specify the value as 0. This parameter is deprecated.
`scid`	INTEGER	Specify the value as 0. This parameter is deprecated.

Returns:
The function returns the protected CLOB value.

Exception:
If the user does not have protect access rights in the policy, UDF terminates with an error message explaining what went wrong.

Example:

sel 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0) "Protected Data";

pty_xmldec

This UDF unprotects the protected CLOB values.

Signature:

pty_xmldec(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Name	Type	Description
`col`	CLOB	Specifies the CLOB data to unprotect.
`dataelement`	VARCHAR	Specifies the name of the data element.
`resultlen`	INTEGER	Specifies the length of the buffer to hold the result.
`communicationid`	INTEGER	Specify the value as 0. This parameter is deprecated.
`scid`	INTEGER	Specify the value as 0. This parameter is deprecated.

Returns:

The function returns the unprotected XML value.
The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select
pty_xmldec( 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0),'AES256',1500,0,0) "UnProtected Data";

pty_xmldecex

This UDF unprotects the protected CLOB value with strong encryption.

Signature:

pty_xmldecex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Name	Type	Description
`col`	CLOB	Specifies the CLOB data to protect.
`dataelement`	VARCHAR	Specifies the name of the data element.
`resultlen`	INTEGER	Specifies the length of the buffer to hold the result.
`communicationid`	INTEGER	Specify the value as 0. This parameter is deprecated.
`scid`	INTEGER	Specify the value as 0. This parameter is deprecated.

Returns:
The function returns the unprotected XML value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select
pty_xmldecex( 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0),'AES256',1500,0,0) "UnProtected Data";

Feedback

Was this page helpful?

Last modified : February 20, 2026