topic Re: How To Parse a XML Column with string data type into multiple sql columns in Data Engineering

How To Parse a XML Column with string data type into multiple sql columns

LearnDB1234 — Thu, 12 Dec 2024 15:25:35 GMT

Hi,

I have a table with XML data in it which is stored in a column with STRING datatype. Can someone please help me on how to parse this XML into multiple sql columns.Below is the sample XML Table & desired output data

Select * from default.SampleData XMLData 'Status': { 'Code': 'Ok', 'Message': None, 'Errors': None }, 'RequestReference': None, 'Response': { 'IdNumber': '295', 'SearchKeys': { 'Subject': { 'FirstName': 'John', 'MiddleName': None, 'Surname': 'Ross', 'DateOfBirth': datetime.datetime(1900, 1, 31, 0, 0), 'Gender': 'M' }, 'Address': { 'AddressLine1': '123 old street', 'AddressLine2': 'high street', 'AddressLine3': '', 'AddressLine4': None, 'AddressLine5': None, 'Postcode': '12345', 'Country': None }, 'Phone': None, 'DrivingLicence': None, 'Bank': None, 'ConsentFlag': True },

Expected output columns

IDNumber FirstName MiddleName LastName DateOfBirth AddressLine1 Phone 295 John Ross 1900-01-01 123 Highstreet None

Can someone please provide any ideas around this.

Re: How To Parse a XML Column with string data type into multiple sql columns

Walter_C — Thu, 12 Dec 2024 15:31:31 GMT

Have you checked the parse method listed in doc https://docs.databricks.com/en/query/formats/xml.html

Re: How To Parse a XML Column with string data type into multiple sql columns

szymon_dybczak — Fri, 13 Dec 2024 08:50:23 GMT

Hi @LearnDB1234 ,

Are you sure that this column stores xml as a string? To me it looks more like json string 🙂
If so, you can use new VARIANT data type through parse_json function:

%sql WITH src AS ( SELECT parse_json('{ "Status": { "Code": "Ok", "Message": null, "Errors": null }, "RequestReference": null, "Response": { "IdNumber": "295", "SearchKeys": { "Subject": { "FirstName": "John", "MiddleName": null, "Surname": "Ross", "DateOfBirth": "1900-01-31T00:00:00", "Gender": "M" }, "Address": { "AddressLine1": "123 old street", "AddressLine2": "high street", "AddressLine3": "", "AddressLine4": null, "AddressLine5": null, "Postcode": "12345", "Country": null }, "Phone": null, "DrivingLicence": null, "Bank": null, "ConsentFlag": true } } }') as json_column ) SELECT json_column:Response.IdNumber::INTEGER as id_number, json_column:Response.SearchKeys.Subject.FirstName::STRING as first_name, json_column:Response.SearchKeys.Subject.MiddleName::STRING as middle_name, json_column:Response.SearchKeys.Subject.Surname::STRING as last_name, json_column:Response.SearchKeys.Subject.DateOfBirth::STRING as date_of_birth, json_column:Response.SearchKeys.Address.AddressLine1::STRING as address_line1, json_column:Response.SearchKeys.Phone::STRING as phone FROM src

Re: How To Parse a XML Column with string data type into multiple sql columns

LearnDB1234 — Fri, 13 Dec 2024 17:05:44 GMT

Even though the data in the column seems like a JSON formatted the data type is string, so using your code and suggestions from databricks below is the code i have tried below code:

SELECT *,

get_json_object(XMLData, '$.Response.IdNumber') AS id_number,

get_json_object(XMLData, '$.Status.Code:') AS Code

FROM default.sampledata;

I get NULL values in the output, am i missing something in here?