Problems with XMLPUBLISH_STREAM in 15.10

Database
Fan

Re: Problems with XMLPUBLISH_STREAM in 15.10

I guess I should tell you the problem I am having we are getting the error below, when attempting to generate the xml from the 2nd part of the process, it uses the SQL that is generated and if I run the SQL in SQL Assistant, it generates the XML lines properly as individual rows in the result set, but when run from BTEQ we get this error every time.

 

BTEQ -- Enter your SQL request or BTEQ command:

.export file=c:\users\xxxxxxxx\documents\XML_OUTPUT.XML
 *** To reset export, type .EXPORT RESET
 BTEQ -- Enter your SQL request or BTEQ command:
.run file=c:\users\xxxxxxxx\documents\XML_PUB3.SQL
 BTEQ -- Enter your SQL request or BTEQ command:
XMLPUBLISH_GENSQL(queryString,xslt,documentSpec,dataTypes)
SELECT T.XMLFRAG FROM ( SELECT * FROM (SELECT CAST('<?xml version="1.0" encoding="UTF-16"?><QuerySchema>' AS VARCHAR(30000)) as XMLFRAG, 0 AS ORDERKEY) AS T1 UNION SELECT CAST('<ROW>'||'<CUSTOMER_ID>'||(CASE WHEN CUSTOMER_ID IS NULL THEN '' ELSE CAST(CUSTOMER_ID AS VARCHAR(40)) END)||'</CUSTOMER_ID>'||'<FIRST_NAME>'||(CASE WHEN FIRST_NAME IS NULL THEN '' ELSE CAST(XMLNORMALIZE(FIRST_NAME) AS VARCHAR(360)) END)||'</FIRST_NAME>'||'<LAST_NAME>'||(CASE WHEN LAST_NAME IS NULL THEN '' ELSE CAST(XMLNORMALIZE(LAST_NAME) AS VARCHAR(360)) END)||'</LAST_NAME>'||'</ROW>' AS VARCHAR(32000)) AS XMLFRAG, 1 AS ORDERKEY FROM(SELECT customer_id, first_name, last_name from customers) AS T2 UNION SELECT * FROM(SELECT '</QuerySchema>' AS XMLFRAG, 2 AS ORDERKEY) AS T3) AS T ORDER BY T.ORDERKEY;

XMLPUBLISH_GENSQL(queryString,xslt,documentSpec,dataTypes)
                  $
 *** Failure 3706 Syntax error: expected something between the beginning of the request and the 'XMLPUBLISH_GENSQL' keyword.
                Statement# 2, Info =19
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

 *** Warning: EOF on INPUT stream.

 

The scripts I am using are as follows:

 

1st BTEQ Script:

.LOGON  xxxxxxx/xxxxxxx,xxxxxx;
DATABASE xxxxxxxx;
.export file=C:\Users\xxxxxxxx\Documents\XML_PUB3.SQL
call TD_SYSXML.XMLPUBLISH_STREAM('SELECT customer_id, first_name, last_name from customers', NULL, NULL);
.export reset

 

2nd BTEQ Script:

.LOGON  xxxxxxxx/xxxxxxxx,xxxxxxx;
.export file=c:\users\xxxxxxx\documents\XML_OUTPUT.XML
.run file=c:\users\xxxxxxx\documents\XML_PUB3.SQL
.export reset

Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

Hi,

 

I am aslo new learner on this. Not sure about the issue. below code is running for me . U can try this . May be proper export file and charset may fix your problem. For more , we need to go through document in Teradata site . 'Teradata XML' document is there. I read from there only.

 

bteq << EOD

.LOGON  xxxxxxx/xxxxxxx,xxxxxx;

.EXPORT DATA FILE=xml_pub
.SET WIDTH 65000
.SET RECORDMODE OFF;

CALL TD_SYSXML.XMLPUBLISH_STREAM('SELECT customer_id, first_name, last_name from customers',NULL,NULL);

.IF ERRORCODE <> 0 THEN .GOTO ErrorExit

.EXPORT RESET

.EXPORT DATA FILE=xml_output
.SET WIDTH 65000
.SET RECORDMODE OFF;
SET SESSION CHARSET "UTF8";
.RUN FILE=xml_pub

.IF ERRORCODE <> 0 THEN .GOTO ErrorExit

.EXPORT RESET

.LABEL NormalExit
.REMARK "////New File generated: RC=0////"
.QUIT 0

.LABEL ErrorExit
.REMARK "////!!! ABORTING: RC=1////"
.REMARK "////!!! New File Generation Failed !!////"
.QUIT 1

EOD

 

Thanks,

/s