cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Not able to load nested XML file with struct type

VishalD
New Contributor

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

1 REPLY 1

jose_gonzalez
Databricks Employee
Databricks Employee

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

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local community—sign up today to get started!

Sign Up Now