Forum Discussion
XML Import - Table Type in Column
The XML import as well as the Parse transformation seem to be VERY particular about 1) Having a well formed XML document with a root, etc all defined. 2) Avoid having empty elements. This seems to cause non-expandable tables inside of the expanded columns. 3) Avoid nesting elements. This seems to be fixable by doing a double level (or deeper if necessary expand).
If anyone knows of a way to get rid of the empty [Table] references left by empty elements? I really want to replace them with a NULL or a blank using M, but I'm just not sure how to do it. Someone referenced a function that someone else created in another thread, but they also said it did not work.
I imported my XML from the Adventureworks database, the HumanResources.JobCandidate.Resume column. I then used Parse > XML from the transform tab. Skills expanded just fine. Employment had nested elements, so the column needs to be further expanded before it can be used, which is fine once it is set up once, it will reapply the steps every time. The problem column is Email. Non existent elements are correctly translated into null which totally makes sense to me. Empty Email elements are translated into empty tables which become [Table] in the reports. I want the Tables to be changed to nulls as well. Any ideas?