CODEWITHSUNDEEP
snowflake-cloud-data-platform
Kar
Kar

Reputation: 1014

Snowflake:How to read the data which is in JSON format but it is wrapped in an XML response

I get the data response from API as XML with JSON data in it

How can I achieve reading the JSON data using Snowflake to load into the table?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <GetFilteredResponse xmlns="Log_EngIP">
            <GetFilteredCustomReportResult>
                <DtoObject>
                    <ReportData>[{"ID":1,"Name":"Default","OwnerID":1,"SortOrder":null,"Terms":"+0"},{"ID":3,"Name":"VIP","OwnerID":1,"SortOrder":null,"Terms":"+30"},{"ID":9,"Name":"Telesphere (EmailAndStatus)","OwnerID":1,"SortOrder":0,"Terms":"+0"},{"ID":10,"Name":"Default","OwnerID":693,"SortOrder":null,"Terms":"+30"}]</ReportData>
                    <NumberOfPages>1</NumberOfPages>
                </DtoObject>
                <Response>Successful</Response>
                <Successful>true</Successful>
            </GetFilteredCustomReportResult>
        </GetFilteredResponse>
    </soap:Body>
</soap:Envelope>

Upvotes: 0

Views: 250

Answers (1)

user13472370
user13472370

Reputation:

You can parse/load the XML, use XML traversal functions to extract the ReportData element value (a JSON string), re-parse the JSON as a variant, then query its fields using JSON traversal functions.

Here's a simple example that assumes the sample provided is positional:

with tbl as (
-- Sample 'table' with 1 row of XML-parsed variant data
  select parse_xml('<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetFilteredResponse xmlns="Log_EngIP"><GetFilteredCustomReportResult><DtoObject><ReportData>[{"ID":1,"Name":"Default","OwnerID":1,"SortOrder":null,"Terms":"+0"},{"ID":3,"Name":"VIP","OwnerID":1,"SortOrder":null,"Terms":"+30"},{"ID":9,"Name":"Telesphere (EmailAndStatus)","OwnerID":1,"SortOrder":0,"Terms":"+0"},{"ID":10,"Name":"Default","OwnerID":693,"SortOrder":null,"Terms":"+30"}]</ReportData><NumberOfPages>1</NumberOfPages></DtoObject><Response>Successful</Response><Successful>true</Successful></GetFilteredCustomReportResult></GetFilteredResponse></soap:Body></soap:Envelope>') xmlv

), first_report_data_json as (

-- Extracting JSON as a string value from XML and then reparsing it as its own variant
-- This uses the symbol style of traversal, but XMLGET/GET can alternatively be used
  select parse_json(xmlv:"$"."$"."$"."$"[0]."$"[0]."$") jsonv from tbl

)
-- Producing rows from the JSON array variant formed above
select e.value:ID, e.value:Name from first_report_data_json, lateral flatten(first_report_data_json.jsonv) e;

Which yields:

+------------+-------------------------------+                                  
| E.VALUE:ID | E.VALUE:NAME                  |
|------------+-------------------------------|
| 1          | "Default"                     |
| 3          | "VIP"                         |
| 9          | "Telesphere (EmailAndStatus)" |
| 10         | "Default"                     |
+------------+-------------------------------+

Note: The access of XML nodes GetFilteredCustomReportResult and further required array-style access ("$"[0]) likely because the schema (readable in the xmlns links) defining it allows for unbounded number of occurrences (i.e. its actually an array of elements, not necessarily a singular one).

In a real-world situation, to be complete, you'll use FLATTEN to break them out to their own whole rows for independent processing. I've omitted that here and just gone after the single element existing in the example provided.

Upvotes: 1

Related Questions