CODEWITHSUNDEEP
xmlsnowflake-cloud-data-platform
Kamalakannan Babuji
Kamalakannan Babuji

Reputation: 67

snowflake XML Parsing returns NULL - SPACE in field name?

I am trying to get the ap id value in below xml but getting NULL back. Please help. If possible please send me a flatten query to flatten the below xml file in snowflake. I am very new to snowflake and any of your help will be much appreciated. Is this because of the space in the field name "ap id"? I doubt that. How to process the field names with space in snowflake for XML files?

select GET(lvl,'@ap id')::integer as "ap id" from (SELECT src:"$" as lvl FROM XMLDATA);

SELECT XMLGET(SRC, 'ap id' ):"@" as "ap id" FROM XMLDATA;

SELECT GET(src, '@ap id')::integer as ap_id FROM XMLDATA;

Upvotes: 1

Views: 784

Answers (1)

waldente
waldente

Reputation: 1424

Can you try:

select src:"$"."@id" as app_id
from xmldata;

Here's a way to find a path by flattening and searching for a known value:

with xmldata as (
    select parse_xml('<amp_ap_detail><ap id="1610">asdf</ap></amp_ap_detail>') src
)
select path, value 
from xmldata, lateral flatten(input=>xmldata.src, recursive=>true) f
where value = 1610;

Which in this case yields:

PATH            VALUE
['$']['@id']    1610

Mapping PATH into Snowflake's field identifier notation...

with xmldata as(
    select parse_xml('<amp_ap_detail><ap id="1610">asdf</ap></amp_ap_detail>') src
)
select src:"$"."@id" as app_id
from xmldata;

Hope that's helpful.

Upvotes: 1

Related Questions