XML Auto Loader rescuedDataColumn Doesn't Rescue Array Fields

[peter_ticker]

Hiya! I'm interested whether anyone has a solution to the following problem. If you load XML using Auto Loader or otherwise and set the schema to be such that a single value is assumed for a given xpath but the actual XML contains multiple values (i.e. should be an array) then the additional values aren't rescued so become irrecoverably dropped.

Example XML data: <Message><Attribute>1</Attribute><Attribute>2</Attribute></Message>

rowTag: Message

Schema applied: Attribute INTEGER (actual schema should be Attribute ARRAY<INTEGER>)

Result: Table containing one column Attribute with 2 as the value and the rescuedDataColumn is empty.

In the above example the value 1 in the additional Attribute tag is dropped and cannot be recovered because it doesn't land in the rescuedDataColumn. I need a way to ensure this data is not dropped because we cannot know whether any given tag in the XML being read has one node corresponding to it or is actually an array.

[Witold]

Then your example XML is invalid, as an XML file has only one root element. Meaning, Message can only be there once. If Message is your row, then there needs to be another root element, e.g:

<Messages>
  <Message>
    <Attribute>1</Attribute>
    <Attribute>2</Attribute>
  </Message>
</Messages>

[peter_ticker]

I'm not sure what your concern is. I could've encapsulated the whole thing in another root element but we receive XML files with one row per file so there is no need for a root element.

[Witold]

Let me rephrase it. You can't use Message as the rowTag, because it's the root element. rowTag implies that it's a tag within the root element, which might occur multiple times. Check the docs around reading and write XML files, there you'll find examples which include a root tag (books) and row tags (book). If you check on this page the docs of from_xml, you'll see that "rowTag option is not applicable" due the exact same reason: You read just a single record.

To solve your issue, you either need to introduce another root tag (which I read is not an option for you), or to find another row tag, which might be Attribute. I hope it helps!