Combining XML files Using SQL

Question

I want to combined mulitple exisiting XML files into one XML output file.

I am a new developer and still learning.

I have a table with four columns - ROWID, XMLMESSAGE, BUS_DTE, DW_LOD.

I want to write a query in teradata that will put several XML messages into one single XML output message.

My current approach is:

SELECT XMLSERIALIZE(
    DOCUMENT XMLELEMENT(NAME "XML_MESSAGES", 
        XMLAGG(XMLMSG)
    ) AS VARCHAR(20000)
) AS transactions_xml
FROM <Database.Table>;

All I get is syntax after syntax error after every edit I make, and I am not sure on how to approach this problem.

Any guidance is hugely appreciated.

You'll need to give us more to work with. Sample data and desired result. — Andrew
– Andrew, Commented Oct 3, 2024 at 15:24

Derviş Kayımbaşıoğlu · Accepted Answer · 2024-10-03 17:00:06Z

0

although I couldn't test it, I tried to fix syntax errors. check this

SELECT XMLSERIALIZE(
         DOCUMENT XMLELEMENT(
           NAME "XML_MESSAGES", 
           XMLAGG(XMLELEMENT(NAME "Message", XMLMESSAGE))
         ) AS VARCHAR(20000)
       ) AS transactions_xml
FROM <Database.Table>;

EDIT: you can also validate XML with where condition

WHERE XMLMESSAGE IS NULL OR NOT (XMLVALIDATE(XMLMESSAGE));

edited Oct 3, 2024 at 17:00

answered Oct 3, 2024 at 13:45

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Wood Over a year ago

Thank you Dervis. I made the edits and the error returned stated 'Error in function XMLELEMENT: Invalid or Null Schema'. What would this represent?

Derviş Kayımbaşıoğlu Over a year ago

there should be "null" values in XMLMESSAGE. You can exclude nulls or you can consider using COALESCE. you can also consider validation XML. I edited my answer to include that in my answer

Collectives™ on Stack Overflow

Combining XML files Using SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related