Problems with XMLPUBLISH_STREAM in 15.10

Database
Enthusiast

Problems with XMLPUBLISH_STREAM in 15.10

I am migrating from the older XML Services in SYSXML to the current TD_SYSXML procedures under v15.10. I  have found no examples of using TD_SYSXML.XMLPUBLISH_STREAM in forums, manuals, or anywhere in Google.  The only information found was for the old services, which are very different.  Teradata support has been unable to provide an example via TAYS ticket for weeks.  Does anyone out there have a working example which uses a stylesheet?  Through experimentation I found the TD manuals are incorrect about how it works and have made some progress getting output.  However, occaisional errors appear which cast its reliability in question.

 

Specifically I am asking:

1. For any examples of XMLPUBLISH_STREAM using a stylesheet.

2. Solutions to occaisional weirdness like the XML version/encoding header tag appearing at the end of the file instead of the top... sometimes.  The same stylesheet and query but limited to fewer rows produces the header correctly at the top.  Full query with thousands of lines and the header is at the end.  Other queries/stylesheets seem fine.  These queries and stylesheets were all valid and producing output via older SYSXML services the day before the upgrade so I know they are valid.

3. Passing a valid query and stylesheet to XMLPUBLISH_STREAM generates output, but passing the same to XMLPUBLISH returns an invalid stylesheet error.

4. It seems we might be the only ones using XMLPUBLISH_STREAM... are we?

Tags (2)

Accepted Solutions
Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

Shivani, here's an example using BTEQ.  There are a lot of little details and caveats I learned making it work, so I hope this helps.  Note that the Teradata XML manual is NOT correct in describing how TD_SYSXML.XMLPUBLISH_STREAM works.  That procedure does not generate XML as output, but instead generates a special query that you have to execute yourself to generate the XML output.

Best wishes,

Rich

--
--  TD_SYSXML.XMLPUBLISH_STREAM Example    Rich Tefft   11/14/2016
--
--  This file give a basic example of using TD_SYSXML.XMLPUBLISH_STREAM in BTEQ.
--
-- The XML_SQL variable contains the entire SQL query we want to output as XML.
-- It CANNOT contain line breaks, so if stored in an external file you must read
-- the .SQL file line-by-line into a shall variable like XML_SQL.  The old XML Services
-- used CLOBs, the new services use VARCHAR. You cannot include an ORDER BY in your query, 
-- as the special query the procedure returns to you has an order by of its own.
-- any 'quotes' in the SQL must be doubled to make BTEQ happy, but at least the procedure
-- does correctly handle special characters like ampersands etc. -- -- The XML_XSL variable contains the entire XML Stylesheet we want to format our -- output with. It CANNOT contain line breaks, so if stored in an external file you must read -- the .SQL file line-by-line into a shall variable like XML_SQL. The old XML Services -- used CLOBs, the new services use VARCHAR. Note you can pass a null here, an TD will -- process your query using a default canonical stylesheet it creates on its own. -- -- The output file XML_PUB.SQL contains a special query that will generate the actual XML output. -- Contrary to what the TD documentation says, XMLPUBLISH_STREAM does not output the actual XML. -- .export file=XML_PUB.SQL set width 65000 call TD_SYSXML.XMLPUBLISH_STREAM(trim('${xml_sql}'), createxml(trim('${xml_xsl}')), null); .export reset -- -- Execute the SELECT in the XL_PUB.SQL file from the previous step to generate the XML output. -- Some caveats: -- -- 1. You may need to clean up the first line or two if you get BTEQ headers. -- -- 2. A single XML record cannot exceed about 65k because that is the max line width for BTEQ. -- -- 3. XML file header tag (Encoding tag=...) should be at the top of the file, but a -- bug in the XMLPUBLISH_STREAM procedure sometimes causes the header tag to appear -- at the end of the file instead. You should check the first line of the .XML file -- to ensure "ENCODING" is there. If not, you need to move the last line of the file -- to the top of the file. -- -- 4. With more complex multi-level XML stylesheets, we have found that XMLPUBLISH_STREAM -- skips some records at lower levels. For example, if there should be (2) 4th level children -- you might only get 1, and the pick seems arbitrary. We have not determined if this is a bug -- in the XMLPUBLISH_STREAM procedure (hope not!) or something in our stylesheets. -- -- Fun, huh? -- .export file=XML_OUTPUT.XML .set width 65000 .run file=XML_PUB.SQL .export reset
1 ACCEPTED SOLUTION
11 REPLIES
Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

I received sample files from TAYS today, and they confirmed the technique we developed was correct.  It also confirmed the TD manual entry to TD_XMLPUBLISH_STREAM does not correctly describe how to use it. I don't see how to attach files here, so if anyone wants the demo files just reply to this post and we can arrange a private exchange.

 

We are still experiencing invalid XML output from the query returned by TD_XMLPUBLISH_STREAM when the stylesheet has more than 2 hierarchy levels.  It puts the encoding header tag at the end, not the top.  We're still trying to determine if other lines in the file are out of order.

Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

Hi,

We have requirement to use xml publishing procedure but couldn;t find any proper sample code how to use it.

Colud you please share any info regarding this. Basically we are looking for hw to define xsl sheet for in parameter.

Does this procedure XMLPUBLISH_STREAM or XMLPUBLISH require 2 OUT parameter.

 

Thanks,

Shivani

Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

Shivani, here's an example using BTEQ.  There are a lot of little details and caveats I learned making it work, so I hope this helps.  Note that the Teradata XML manual is NOT correct in describing how TD_SYSXML.XMLPUBLISH_STREAM works.  That procedure does not generate XML as output, but instead generates a special query that you have to execute yourself to generate the XML output.

Best wishes,

Rich

--
--  TD_SYSXML.XMLPUBLISH_STREAM Example    Rich Tefft   11/14/2016
--
--  This file give a basic example of using TD_SYSXML.XMLPUBLISH_STREAM in BTEQ.
--
-- The XML_SQL variable contains the entire SQL query we want to output as XML.
-- It CANNOT contain line breaks, so if stored in an external file you must read
-- the .SQL file line-by-line into a shall variable like XML_SQL.  The old XML Services
-- used CLOBs, the new services use VARCHAR. You cannot include an ORDER BY in your query, 
-- as the special query the procedure returns to you has an order by of its own.
-- any 'quotes' in the SQL must be doubled to make BTEQ happy, but at least the procedure
-- does correctly handle special characters like ampersands etc. -- -- The XML_XSL variable contains the entire XML Stylesheet we want to format our -- output with. It CANNOT contain line breaks, so if stored in an external file you must read -- the .SQL file line-by-line into a shall variable like XML_SQL. The old XML Services -- used CLOBs, the new services use VARCHAR. Note you can pass a null here, an TD will -- process your query using a default canonical stylesheet it creates on its own. -- -- The output file XML_PUB.SQL contains a special query that will generate the actual XML output. -- Contrary to what the TD documentation says, XMLPUBLISH_STREAM does not output the actual XML. -- .export file=XML_PUB.SQL set width 65000 call TD_SYSXML.XMLPUBLISH_STREAM(trim('${xml_sql}'), createxml(trim('${xml_xsl}')), null); .export reset -- -- Execute the SELECT in the XL_PUB.SQL file from the previous step to generate the XML output. -- Some caveats: -- -- 1. You may need to clean up the first line or two if you get BTEQ headers. -- -- 2. A single XML record cannot exceed about 65k because that is the max line width for BTEQ. -- -- 3. XML file header tag (Encoding tag=...) should be at the top of the file, but a -- bug in the XMLPUBLISH_STREAM procedure sometimes causes the header tag to appear -- at the end of the file instead. You should check the first line of the .XML file -- to ensure "ENCODING" is there. If not, you need to move the last line of the file -- to the top of the file. -- -- 4. With more complex multi-level XML stylesheets, we have found that XMLPUBLISH_STREAM -- skips some records at lower levels. For example, if there should be (2) 4th level children -- you might only get 1, and the pick seems arbitrary. We have not determined if this is a bug -- in the XMLPUBLISH_STREAM procedure (hope not!) or something in our stylesheets. -- -- Fun, huh? -- .export file=XML_OUTPUT.XML .set width 65000 .run file=XML_PUB.SQL .export reset
ges
Visitor

Re: Problems with XMLPUBLISH_STREAM in 15.10

If you still have sample files, I would appreciate a copy.  I'm just starting to try and publish a data pull to XML.  Anything would be greatly appreciated.

Fan

Re: Problems with XMLPUBLISH_STREAM in 15.10

If you still have a copy of the example, please forward to me, too.  I get to the point of running .run file=XML_PUB.SQL and then get an error, if I run the SQL in SQL_ASSISTANT, it get .txt members with the correct XML in them, but XML_PUB.SQL gets an error.

Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

Hi ,

 

sorry for late reply. check this if it can help .

 

cat xml_pub.sql

xml_sql='SELECT code FROM db_views.tablename GROUP by 1 sample 2

Note : make sure SQL should be in single line.
===========================
This step will generate SQL which will used to generate xml file.( Note : xml file will be in default format. )
==============================

.logon tdlogon/tduser,tdpaswd;
.EXPORT File = xml_pub.sql
set width 65000
call TD_SYSXML.XMLPUBLISH_STREAM(trim('${xml_sql}'), null , null);
.export reset


Note : make sure User TD user have access on TD_SYSXML.
==============================
This step will generate XML file
==============================

.export file=XML_OUTPUT.XML
.set width 65000
.run file=XML_PUB.SQL
.export reset

.logoff

echo "xml file"
cat XML_OUTPUT.XML

.exit

Fan

Re: Problems with XMLPUBLISH_STREAM in 15.10

how do you set the variable values for xml_sql in the BTEQ script or before it?

Enthusiast

Re: Problems with XMLPUBLISH_STREAM in 15.10

set xml_sql before BTEQ  or u can directly write SQL as a parammeter .

 

eg. CALL TD_SYSXML.XMLPUBLISH_STREAM('SELECT code from db_view.tablename GROUP BY 1' ,NULL,NULL);

Fan

Re: Problems with XMLPUBLISH_STREAM in 15.10

is there away besides using shell variables to value a variable or input file in BTEQ with a query line, Stylesheet, etc., then use that files or variables contents in xmlpu