Snowflake XML parsing: extract attribute of a tag value in snowflake sql-CodePudding

  <ROOM name="Closet">
    <ITEMS>
      <ITEM ageM="6" brand="Ross'" costToReplace="25" desc="Sweater shawl" id="11" lineNum="11" method="2" purchasedFrom="Ross'" qtyLost="1" use="1">
        <IMAGES></IMAGES>
        <ITEM_RECEIPT_INFOS></ITEM_RECEIPT_INFOS>
      </ITEM>
      <ITEM ageY="2" brand="GAP" costToReplace="20" desc="white long sleeve shirt" id="12" lineNum="12" method="2" purchasedFrom="GAP" qtyLost="1">
        <IMAGES></IMAGES>
        <ITEM_RECEIPT_INFOS></ITEM_RECEIPT_INFOS>
      </ITEM>
    </ITEMS>
  </ROOM>
  <ROOM name="Kitchen">
    <ITEMS>
      <ITEM ageM="6" ageY="1" brand="Pier 1 Imports" costToReplace="30" desc="Wine decanter" id="13" lineNum="13" method="3" purchasedFrom="Pier 1 Imports" qtyLost="1" use="1">
        <IMAGES></IMAGES>
        <ITEM_RECEIPT_INFOS></ITEM_RECEIPT_INFOS>
      </ITEM>
    </ITEMS>
  </ROOM>
</ROOMS>

The ID return values are supposing 11, 12, 13, but I only got 11, 12.

My code:

Column name is room, Table name is item_xml

select 
  rs.value:"@id" ::string
from item_xml,  LATERAL FLATTEN( INPUT => XMLGET(XMLGET(item_xml.room, 'ROOM'),'ITEMS'):"$") rs;

Could someone please help me? Thank you so much!

CodePudding user response：

The problem is you have two arrays that you are want to traverse but only one FLATTEN,

thus you need another FLATEN, but the trick here, is that "Kitchen" has only one item in ITEMS which most XML parse incorrectly map to an object (aka this happens in snowflake also).

Thus you have to convert the output of "items" from "Room" to an array before you use it:

select
    i.value:"@id" ::string
from item_xml
    ,LATERAL FLATTEN( INPUT => room:"$") as r
    ,LATERAL FLATTEN( INPUT => to_array(XMLGET(r.value,'ITEMS'):"$") ) i
;

gives:

I.VALUE:"@ID" ::STRING
11
12
13

techincally you really should wrap all arrays, so when the data is a single item, you SQL does not explode. thus you solution should be:

select
    i.value:"@id" ::string
from item_xml
    ,LATERAL FLATTEN( INPUT => to_array(room:"$")) as r
    ,LATERAL FLATTEN( INPUT => to_array(XMLGET(r.value,'ITEMS'):"$") ) i
;