avrm91
Databricks Partner

@Retired_mod Thanks a lot.
I found an issue in from_xml function.
I posted above:

 

SELECT
  from_xml(CONCAT('<ABSENDER>', ABSENDER, '</ABSENDER>'), 
    schema_of_xml('
		<ABSENDER>
			<RZLZ>R00000001</RZLZ>
			<NAME>Informatik GmbH</NAME>
			<STRASSE>Muster-Allee 90</STRASSE>
			<PLZ>60486</PLZ>
			<ORT>Frankfurt a.M.</ORT>
			<LAND>DE</LAND>
			<KONTAKT>
				<ANREDE>Herr</ANREDE>
				<VORNAME>Max</VORNAME>
				<ZUNAME>Mustermann</ZUNAME>
				<TELEFON>xxxxx/xxxx-xx</TELEFON>
				<FAX>xxx/xxxxx-xx</FAX>
				<EMAIL>max.mustermann@informatik.de</EMAIL>
			</KONTAKT>
		</ABSENDER>'))  AS ABSENDER_XML
FROM conformed

 

But it gave me just a bad result with most of the attributes not mapped. 

 

object
KONTAKT: null
LAND: null
NAME: null
ORT: null
PLZ: null
RZLZ: "R00000001"
STRASSE: null

 

This is not the real data as I "anonymized" the data before posting here.
So, the reason is that the 
<NAME>Informatik GmbH & Co.KG</NAME> contains an "&" what will make the from_xml function fail. 
I used a simple replace to "fix" it (make it work for now)

 

%sql
replace(ABSENDER, '&', '')

 

Now the object looks like that.

 

object
KONTAKT: 
	ANREDE: "Herr"
	EMAIL: "max.mustermann@informatik.de"
	FAX: "xxx/xxxxx-xx"
	TELEFON: "xxxxx/xxxx-xx"
	VORNAME: "Max"
	ZUNAME: "Mustermann"
LAND: "DE"
NAME: "Informatik GmbH  Co.KG"
ORT: "Frankfurt a.M."
PLZ: "60486"
RZLZ: "R00000001"
STRASSE: "Muster-Allee 90"

 

I found it after several hours of debugging. Sad it gave me no error. It just did not map the object correctly.
Hope you can address this, as in the documentation it says, this feature is in public preview
from_xml function - Azure Databricks - Databricks SQL | Microsoft Learn