Problems with XMLPUBLISH_STREAM - xml exportation

Database
Enthusiast

Problems with XMLPUBLISH_STREAM - xml exportation

Hi All, I have a problem with the XMLPUBLISH_STREAM procedure, i'm trying to export a query result to XML. With the XMLPUBLISH_STREAM procedure, it works perfectly if I don't want any XSL transformation. But once I add my XSL file, only one random ROW is returned following the mapping I want.
I have the feeling that the xsl:for-each tag doesn't work.
I read on this forum (https://community.teradata.com/t5/Database/Problems-with-XMLPUBLISH-STREAM-in-15-10/m-p/68477) that sometimes only one ROW is selected with this procedure.
There is an example of my query :
.export DATA file =/cop/data/survey2/XML_PUB.SQL

.set width 100000

.set NULL ' '

.set TITLEDASHES OFF

.set recordmode OFF

call TD_SYSXML.XMLPUBLISH_STREAM( ${QueryString},createxml(${XSL_with_for-each}), null);

.export reset

.export DATA file = /cop/data/survey2/XML_OUTPUT.XML

.set width 100000

.set TITLEDASHES OFF

.set recordmode OFF

.run file = /cop/data/survey2/XML_PUB.SQL

.export reset
I also tried with the TD_SYSXML.XMLPUBLISH procedure with this code :
.export DATA file =/cop/data/survey2/XML_PUB.SQL

.set width 100000

.set NULL ' '

.set TITLEDASHES OFF

.set recordmode OFF

call TD_SYSXML.XMLPUBLISH_STREAM(${QueryString},createxml(${XSL_with_for-each}));

.export reset

.export DATA file = /cop/data/survey2/XML_OUTPUT.XML

.set width 100000

.set TITLEDASHES OFF

.set recordmode OFF

.run file = /cop/data/survey2/XML_PUB.SQL

.export reset
Problem is that the procedure returns a failure telling that the positional assignment list has to few values. I don't understand it because the Teradata documentation specifies that TD_SYSXML.XMLPUBLISH only two parameters IN (queryString and XML)
Is there someone who can help me or had the same problem before ?
Thanks all in advance
Regards,
Mouhcine


Accepted Solutions
Teradata Employee

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Looks to me like you probably want the <invitationContact> to occur once for each customer within <invitationContacts>. So instead of just

 

 <invitationContact>

you would use

 <invitationContact teradata_group="CUST_NUM">

You might also need to move the for-each tags inside the <invitationContacts> tags; not sure about that.

 

1 ACCEPTED SOLUTION
7 REPLIES 7
Teradata Employee

Re: Problems with XMLPUBLISH_STREAM - xml exportation

For XSLT v1 you should only use for-each at the "top" level to iterate over the result set rows. You need the teradata_group annotation to output repeating elements (mapping data from multiple resut set rows to elements at the same level within a hierarchy).

 

 

 

Enthusiast

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Hi Fred, 
Thanks for your help and reply, I just want to learn more, so I have some questions : 
- What do you mean by "Top" Level ? Do you have an example ? 

- What's the Teradata Group annotation ? 

 

Here you can find an example of my XSLT query into XMLPUBLISH_STREAM

call TD_SYSXML.XMLPUBLISH_STREAM('SELECT * FROM D0_BUS_EUDSWRV.FACT_EVT_R_CUST_D_SURV_CTT_EBU',createxml(

'<?xml version="1.0" encoding="UTF-16"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

    <xsl:template match="/">

               <xsl:for-each select="QuerySchema/ROW">

                       <invitationContacts>

                              <invitationContact>

                                      <contactInfo>

                                             <customerid>

                                                     <xsl:value-of select="CUST_NUM"/>

                                             </customerid>

                                             <clusterid>

                                                     <xsl:value-of select="CLUS_NUM"/>

                                             </clusterid>

                                      </contactInfo>

                              </invitationContact>

                       </invitationContacts>

               </xsl:for-each>

        </xsl:template>

</xsl:stylesheet>'), null);

Thanks for your help and time.

 

Best regards, 

Teradata Employee

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Looks to me like you probably want the <invitationContact> to occur once for each customer within <invitationContacts>. So instead of just

 

 <invitationContact>

you would use

 <invitationContact teradata_group="CUST_NUM">

You might also need to move the for-each tags inside the <invitationContacts> tags; not sure about that.

 

Enthusiast

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Ohhh Thanks Fred for your help, you're the best !!! 

Finally that works Smiley Very Happy 
Thank you so much !!! 

 

Have a nice day Fred Smiley Very Happy

Enthusiast

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Hi, 

I just have another question please : 

 

The procedure is working, but when we set the queryString parameter with more than 20 columns to select, the procedure is executed, but the query created by the procedure returns an error : Failure 3798 A column or character expression is larger than the max size.

I checked that every value selected is clearly not larger than the columns authorized length. (the procedure makes a cast in VARCHAR for every column and the length autorized is always way larger than the values in it)

I don't understand where the problem is.

Is there a global query length authorized ? The query created by the XMLPUBLISH_STREAM procedure with my parameters is approximately 12800 characters long.

Thanks for your answer

Teradata Employee

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Sounds like the sum of the max VARCHAR lengths being concatenated exceeds the limit for a VARCHAR column.

Not a very satisfying solution, but you could perhaps modify the generated query to have the CASTs use more reasonable (but large enough) length values.

Enthusiast

Re: Problems with XMLPUBLISH_STREAM - xml exportation

Excellent !! That works Smiley Very Happy

 

Thank you so much Fred for your help and time it was a real pleasure Smiley Very Happy

 

Take care