topic NULL instead of empty string as output from tExtractXMLField in Talend Studio

NULL instead of empty string as output from tExtractXMLField

Vlatko — Fri, 15 Nov 2024 22:23:28 GMT

After flattening of XML-data in tExtractXMLField, all empty XML-elements are converted to empty strings.

It is wanted to have NULL instead of empty strings in the target database.

Do we have a parameter in Talend or another way of controlling how empty XML-elements are converted in tExtractXMLField?

Re: NULL instead of empty string as output from tExtractXMLField

Anonymous — Tue, 08 Nov 2022 02:22:48 GMT

There isn't a parameter to convert empty elements to NULL in tExtractXMLField.

A workaround is to add another tMap component to convert the empty string to "NULL" like the below : row.xxx.isEmpty()?"NULL":row4.xxx

Re: NULL instead of empty string as output from tExtractXMLField

Vlatko — Tue, 08 Nov 2022 04:24:05 GMT

Thank you for the answer!

I know that, but the problem is that we have a couple of hundreds of columns per XML-schema, and as of today we have about 100 different types of XML-schemas. It would be 15-20.000 such tests... 🙂

Re: NULL instead of empty string as output from tExtractXMLField

Anonymous — Tue, 08 Nov 2022 08:07:58 GMT

Another possible solution is to remove the empty XML-elements node in the XML-schema content, then tExtractXMLField will get the null value for the empty element fields

Re: NULL instead of empty string as output from tExtractXMLField

Vlatko — Tue, 08 Nov 2022 08:22:41 GMT

Thanks again!

The case is that the input will always contain all the fields from XSD schema. I.e. all XML-elements are mandatory in all types of XML Schemas that we receive. It cannot be changed.

So, the only solution would be if we can in a safe way remove the empty XML-elements from the input string containing the XML. No doubt it's possible to write such a Regex replace expression. I can do it, but I wonder if you possibly already have done it yourself before? Do you maybe have a complete tReplace doing that? I.e. removing <xxx></xxx> and <xxx/> and... is there other possibilities...?

Anyway, thank you for your help, now I am at least sure that there is no parameters in Talend, and that I have to remove the empty fields from the XML before it comes to the tExtractXMLField.

Best regards

Vlatko

Re: NULL instead of empty string as output from tExtractXMLField

Vlatko — Tue, 08 Nov 2022 21:48:15 GMT

Here is one possible solution:

Re: NULL instead of empty string as output from tExtractXMLField

Vlatko — Tue, 08 Nov 2022 21:49:09 GMT

And as text:

"<\\s*[^>/]*>(( )*|\\s*)</\\s*[^></]*>"

"<.*/>"