Not able to load nested XML file with struct type

VishalD — Mon, 22 Jan 2024 17:13:41 GMT

Hello Experts,

I am trying to load XML with struct type and having XSI type attribute. below is sample XML format:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="https://api.example.com" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<ns1:wsdlGetEmployeesV2Response xmlns:ns1="http://schemas.xmlsoap.org/soap/envelope/">
<return xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="tns:Employee[3]">
<item xsi:type="tns:Employee">
<id xsi:type="xsd:int">1111</id>
<SerialNo xsi:type="xsd:string">0001A</badgeNo>
<additionalFields xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="tns:AdditionalFieldData[4]">
<item xsi:type="tns:AdditionalFieldData">
<key xsi:type="xsd:string">employeecc</key>
<value xsi:type="xsd:string"/>
</item>
</additionalFields>
<grpRoles xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="tns:GrpRoles[2]">
<item xsi:type="tns:GrpRoles">
<groupId xsi:type="xsd:int">9999</groupId>
<groupName xsi:type="xsd:string">Test group</groupName>
<extGroupId xsi:type="xsd:string"/>
</item>
</grpRoles>
<ts xsi:type="xsd:dateTime">2011-110-02T12:18:42+01:00</ts>
</item>
</return>
</ns1:wsdlGetEmployeesV2Response>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

I tried using xml.etree.ElementTree and pandas dataframe, but we have to define schema while loading dataframe to table. Is there any way to load above XML using infer schema?

Regards,

Vishal

Re: Not able to load nested XML file with struct type

jose_gonzalez — Mon, 22 Jan 2024 23:18:24 GMT

You can try to use from_xml() function, here is the link to the docs https://docs.databricks.com/en/sql/language-manual/functions/from_xml.html

topic Not able to load nested XML file with struct type in Data Engineering

Not able to load nested XML file with struct type

Re: Not able to load nested XML file with struct type