Блог

Главная - Вопросы по программированию - Анализ XML-файла Snowflake, если элемент не существует

Анализ XML-файла Snowflake, если элемент не существует

#xml #join #snowflake-cloud-data-platform #lateral

#xml #Присоединиться #снежинка-облако-платформа данных #боковой

Вопрос:

Я написал следующий XML-запрос для анализа XML-файла в моей базе данных Snowflake:

    SELECT XMLGET( prodCstmsHdr.value, 'prodCd' )
FROM (SELECT XMLDOC
          FROM RAW.WE_BREXIT_TRADE_TERMS
       WHERE WE_BREXIT_TRADE_TERMS_UID = 
                (SELECT MAX(WE_BREXIT_TRADE_TERMS_UID) 
                    FROM RAW.WE_BREXIT_TRADE_TERMS
                    WHERE WE_BREXIT_TRADE_TERMS_UID = 1623
                )
        ) btt
    INNER JOIN LATERAL FLATTEN(btt.xmldoc:"$") body
    INNER JOIN LATERAL FLATTEN(body.VALUE:"$") prodCstmsHdr
    INNER JOIN LATERAL FLATTEN(prodCstmsHdr.VALUE:"$") prodClsfnDtl
    INNER JOIN LATERAL FLATTEN(prodClsfnDtl.VALUE:"$") prodClsfnItm
    INNER JOIN LATERAL FLATTEN(prodClsfnItm.VALUE:"$") prodClsfnHTSGrp
    LEFT JOIN LATERAL FLATTEN(prodClsfnItm.VALUE:"$") prodMatlCntnt
WHERE 1=1
  AND GET(prodCstmsHdr.value, '@') = 'prodCstmsHdr'
  AND GET(prodClsfnDtl.value, '@') = 'prodClsfnDtl'
  AND GET(prodClsfnItm.value, '@') = 'prodClsfnItm'
  AND GET(prodClsfnHTSGrp.value, '@') = 'prodClsfnHTSGrp'
  **AND GET(prodMatlCntnt.value, '@') = 'prodMatlCntnt'**
  AND XMLGET( prodCstmsHdr.value, 'prodCd' ):"$"::STRING IN ( '169831A', '5700123')

со следующим XML-файлом:

 <?xml version="1.0"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <ns2:getProductClassificationUpdatesResponse xmlns:ns2="http://xmlns.int.XXXXX.com/customs/globalproductdetails/v1" xmlns:ns3="http://customs.XXXX.com/webservice/soapfault">
                <prodCstmsHdr>
                    <brand>Brand 1</brand>
                    <company>company name</company>
                    <divNm>20</divNm>
                    <divisionDescription>FOOTWEAR</divisionDescription>
                    <gndrAgeDesc>ADULT UNISEX</gndrAgeDesc>
                    <gndrAgeNm>01</gndrAgeNm>
                    <prodCd>169831A</prodCd>
                    <prodClsfnDtl>
                        <cmpndTypeNm>IND</cmpndTypeNm>
                        <imprtCtryCd>EU</imprtCtryCd>
                        <prodClsfnItm>
                            <lastUpdatedDate>2020-05-13T12:15:32.102-07:00</lastUpdatedDate>
                            <primFlag>Y</primFlag>
                            <prodClsfnHTSGrp>
                                <fromPrc>0</fromPrc>
                                <fromSz>0C</fromSz>
                                <hts>64039111111</hts>
                                <prcDirection>UP</prcDirection>
                                <szDirection>ABOVE</szDirection>
                            </prodClsfnHTSGrp>
                            <valPctg>100</valPctg>
                            <valTypeNm>BY PERCENTAGE</valTypeNm>
                        </prodClsfnItm>
                        <prodTypeNm>Inline</prodTypeNm>
                    </prodClsfnDtl>
                    <prodPoDtl>
                        <originCountries>US</originCountries>
                        <uom>PR</uom>
                    </prodPoDtl>
                    <seasonNm>Season 2020</seasonNm>
                    <silDesc>Sil description</silDesc>
                    <silNm>020</silNm>
                    <sportActyDesc>Football</sportActyDesc>
                    <sportActyNm>04</sportActyNm>
                </prodCstmsHdr>
                <prodCstmsHdr>
                    <brand>Brand 1</brand>
                    <company>Company name</company>
                    <divNm>20</divNm>
                    <divisionDescription>FOOTWEAR</divisionDescription>
                    <gndrAgeDesc>WOMENS</gndrAgeDesc>
                    <gndrAgeNm>22</gndrAgeNm>
                    <prodCd>5700123</prodCd>
                    <prodClsfnDtl>
                        <cmpndTypeNm>INDIVIDUAL</cmpndTypeNm>
                        <imprtCtryCd>EU</imprtCtryCd>
                        <prodClsfnItm>
                            <itmTypeNm>SHOE</itmTypeNm>
                            <lastUpdatedDate>2020-05-07T12:03:38.933-07:00</lastUpdatedDate>
                            <primFlag>Y</primFlag>
                            <prodClsfnHTSGrp>
                                <fromPrc>0</fromPrc>
                                <fromSz>0C</fromSz>
                                <hts>64041111000</hts>
                                <htsDesc>OTHER</htsDesc>
                                <prcDirection>UP</prcDirection>
                                <szDirection>ABOVE</szDirection>
                            </prodClsfnHTSGrp>
                            <prodMatlCntnt>
                                <athleticFootwear>N</athleticFootwear>
                                <constrCharacteristicDesc>Lined</constrCharacteristicDesc>
                                <constrProcessDesc>V</constrProcessDesc>
                                <coversAnkle>Y</coversAnkle>
                                <factoryCode>9K</factoryCode>
                                <fastenerTypeDesc>Lacing</fastenerTypeDesc>
                                <heelStrapFlag>N</heelStrapFlag>
                                <liningPrimary>
                                    <fiber1>Cotton</fiber1>
                                    <fiber1Percent>100</fiber1Percent>
                                    <material>Textile</material>
                                </liningPrimary>
                                <liningSecondary>
                                    <fiber1>Synthetic Leather</fiber1>
                                    <material>Synthetic</material>
                                </liningSecondary>
                                <measurementSummary>
                                    <coatedLeatherPct>0.0</coatedLeatherPct>
                                    <leatherPct>0.0</leatherPct>
                                    <otherPct>0.0</otherPct>
                                    <summMethodCd>A</summMethodCd>
                                    <syntheticPct>0.0</syntheticPct>
                                    <textilePct>100.0</textilePct>
                                </measurementSummary>
                                <midsole>
                                    <primaryMaterial>EVA</primaryMaterial>
                                </midsole>
                                <onePieceFtw>N</onePieceFtw>
                                <onePieceSole>N</onePieceSole>
                                <openHeelFlag>N</openHeelFlag>
                                <openToeFlag>N</openToeFlag>
                                <outsole>
                                    <primaryMaterial>Rubber</primaryMaterial>
                                    <primaryMaterialPercent>100</primaryMaterialPercent>
                                </outsole>
                                <plugsBottom>N</plugsBottom>
                                <plugsSide>N</plugsSide>
                                <primTechFeatureDesc>Low Density Polymer</primTechFeatureDesc>
                                <sockliner>
                                    <primaryMaterial>Textile</primaryMaterial>
                                </sockliner>
                                <uprLthrNm/>
                                <uprMajority>Textile</uprMajority>
                                <uprOther/>
                                <uprSynthetic/>
                                <uprTextile>
                                    <primaryMaterial>Polyester</primaryMaterial>
                                    <primaryMaterialPercent>100</primaryMaterialPercent>
                                </uprTextile>
                            </prodMatlCntnt>
                            <valPctg>100</valPctg>
                            <valTypeNm>BY PERCENTAGE</valTypeNm>
                        </prodClsfnItm>
                        <prodTypeNm>Inline</prodTypeNm>
                    </prodClsfnDtl>
                    <prodPoDtl>
                        <originCountries>VN</originCountries>
                        <uom>PR</uom>
                    </prodPoDtl>
                    <seasonNm>Season 2020</seasonNm>
                    <silDesc>Sil description</silDesc>
                    <silNm>020</silNm>
                    <sportActyDesc>CASUAL/LEISURE</sportActyDesc>
                    <sportActyNm>29</sportActyNm>
                </prodCstmsHdr>
        </ns2:getProductClassificationUpdatesResponse>
    </S:Body>
</S:Envelope>

Поскольку в продукте 169831A отсутствует узел «prodMatlCntnt», данные не будут отображаться.
Удаление условия «И GET(prodMatlCntnt.value, ‘@’) = ‘prodMatlCntnt'» приведет к дублированию. Обычно это не проблема, но у меня в файле больше узлов, как вы можете видеть, и выполнение запроса занимает целую вечность.

Ответ №1:

Решение проблемы, как описано, состоит в том, чтобы избегать выполнения всех внутренних объединений, которые создают все дубликаты.

В представленной задаче нет необходимости смотреть на внутренние значения. Пожалуйста, перефразируйте проблему, если какое-либо из этих значений действительно требуется.

Решение:

 SELECT XMLGET( prodCstmsHdr.value, 'prodCd' )
FROM (SELECT XMLDOC
          FROM xml
       -- WHERE WE_BREXIT_TRADE_TERMS_UID = 
       --          (SELECT MAX(WE_BREXIT_TRADE_TERMS_UID) 
       --              FROM RAW.WE_BREXIT_TRADE_TERMS
       --              WHERE WE_BREXIT_TRADE_TERMS_UID = 1623
       --          )
        ) btt
    INNER JOIN LATERAL FLATTEN(btt.xmldoc:"$") body
    INNER JOIN LATERAL FLATTEN(body.VALUE:"$") prodCstmsHdr
    -- INNER JOIN LATERAL FLATTEN(prodCstmsHdr.VALUE:"$") prodClsfnDtl
    -- INNER JOIN LATERAL FLATTEN(prodClsfnDtl.VALUE:"$") prodClsfnItm
    -- INNER JOIN LATERAL FLATTEN(prodClsfnItm.VALUE:"$") prodClsfnHTSGrp
    -- LEFT JOIN LATERAL FLATTEN(prodClsfnItm.VALUE:"$") prodMatlCntnt
WHERE 1=1
  -- AND GET(prodCstmsHdr.value, '@') = 'prodCstmsHdr'
  -- AND GET(prodClsfnDtl.value, '@') = 'prodClsfnDtl'
  -- AND GET(prodClsfnItm.value, '@') = 'prodClsfnItm'
  -- AND GET(prodClsfnHTSGrp.value, '@') = 'prodClsfnHTSGrp'
  --
  -- AND GET(prodMatlCntnt.value, '@') = 'prodMatlCntnt'
  --
AND XMLGET( prodCstmsHdr.value, 'prodCd' ):"$"::STRING IN ( '169831A', '5700123')

введите описание изображения здесь

Постановка:

 create or replace temp table xml as
select PARSE_XML('<?xml version="1.0"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <ns2:getProductClassificationUpdatesResponse xmlns:ns2="http://xmlns.int.XXXXX.com/customs/globalproductdetails/v1" xmlns:ns3="http://customs.XXXX.com/webservice/soapfault">
                <prodCstmsHdr>
                    <brand>Brand 1</brand>
                    <company>company name</company>
                    <divNm>20</divNm>
                    <divisionDescription>FOOTWEAR</divisionDescription>
                    <gndrAgeDesc>ADULT UNISEX</gndrAgeDesc>
                    <gndrAgeNm>01</gndrAgeNm>
                    <prodCd>169831A</prodCd>
                    <prodClsfnDtl>
                        <cmpndTypeNm>IND</cmpndTypeNm>
                        <imprtCtryCd>EU</imprtCtryCd>
                        <prodClsfnItm>
                            <lastUpdatedDate>2020-05-13T12:15:32.102-07:00</lastUpdatedDate>
                            <primFlag>Y</primFlag>
                            <prodClsfnHTSGrp>
                                <fromPrc>0</fromPrc>
                                <fromSz>0C</fromSz>
                                <hts>64039111111</hts>
                                <prcDirection>UP</prcDirection>
                                <szDirection>ABOVE</szDirection>
                            </prodClsfnHTSGrp>
                            <valPctg>100</valPctg>
                            <valTypeNm>BY PERCENTAGE</valTypeNm>
                        </prodClsfnItm>
                        <prodTypeNm>Inline</prodTypeNm>
                    </prodClsfnDtl>
                    <prodPoDtl>
                        <originCountries>US</originCountries>
                        <uom>PR</uom>
                    </prodPoDtl>
                    <seasonNm>Season 2020</seasonNm>
                    <silDesc>Sil description</silDesc>
                    <silNm>020</silNm>
                    <sportActyDesc>Football</sportActyDesc>
                    <sportActyNm>04</sportActyNm>
                </prodCstmsHdr>
                <prodCstmsHdr>
                    <brand>Brand 1</brand>
                    <company>Company name</company>
                    <divNm>20</divNm>
                    <divisionDescription>FOOTWEAR</divisionDescription>
                    <gndrAgeDesc>WOMENS</gndrAgeDesc>
                    <gndrAgeNm>22</gndrAgeNm>
                    <prodCd>5700123</prodCd>
                    <prodClsfnDtl>
                        <cmpndTypeNm>INDIVIDUAL</cmpndTypeNm>
                        <imprtCtryCd>EU</imprtCtryCd>
                        <prodClsfnItm>
                            <itmTypeNm>SHOE</itmTypeNm>
                            <lastUpdatedDate>2020-05-07T12:03:38.933-07:00</lastUpdatedDate>
                            <primFlag>Y</primFlag>
                            <prodClsfnHTSGrp>
                                <fromPrc>0</fromPrc>
                                <fromSz>0C</fromSz>
                                <hts>64041111000</hts>
                                <htsDesc>OTHER</htsDesc>
                                <prcDirection>UP</prcDirection>
                                <szDirection>ABOVE</szDirection>
                            </prodClsfnHTSGrp>
                            <prodMatlCntnt>
                                <athleticFootwear>N</athleticFootwear>
                                <constrCharacteristicDesc>Lined</constrCharacteristicDesc>
                                <constrProcessDesc>V</constrProcessDesc>
                                <coversAnkle>Y</coversAnkle>
                                <factoryCode>9K</factoryCode>
                                <fastenerTypeDesc>Lacing</fastenerTypeDesc>
                                <heelStrapFlag>N</heelStrapFlag>
                                <liningPrimary>
                                    <fiber1>Cotton</fiber1>
                                    <fiber1Percent>100</fiber1Percent>
                                    <material>Textile</material>
                                </liningPrimary>
                                <liningSecondary>
                                    <fiber1>Synthetic Leather</fiber1>
                                    <material>Synthetic</material>
                                </liningSecondary>
                                <measurementSummary>
                                    <coatedLeatherPct>0.0</coatedLeatherPct>
                                    <leatherPct>0.0</leatherPct>
                                    <otherPct>0.0</otherPct>
                                    <summMethodCd>A</summMethodCd>
                                    <syntheticPct>0.0</syntheticPct>
                                    <textilePct>100.0</textilePct>
                                </measurementSummary>
                                <midsole>
                                    <primaryMaterial>EVA</primaryMaterial>
                                </midsole>
                                <onePieceFtw>N</onePieceFtw>
                                <onePieceSole>N</onePieceSole>
                                <openHeelFlag>N</openHeelFlag>
                                <openToeFlag>N</openToeFlag>
                                <outsole>
                                    <primaryMaterial>Rubber</primaryMaterial>
                                    <primaryMaterialPercent>100</primaryMaterialPercent>
                                </outsole>
                                <plugsBottom>N</plugsBottom>
                                <plugsSide>N</plugsSide>
                                <primTechFeatureDesc>Low Density Polymer</primTechFeatureDesc>
                                <sockliner>
                                    <primaryMaterial>Textile</primaryMaterial>
                                </sockliner>
                                <uprLthrNm/>
                                <uprMajority>Textile</uprMajority>
                                <uprOther/>
                                <uprSynthetic/>
                                <uprTextile>
                                    <primaryMaterial>Polyester</primaryMaterial>
                                    <primaryMaterialPercent>100</primaryMaterialPercent>
                                </uprTextile>
                            </prodMatlCntnt>
                            <valPctg>100</valPctg>
                            <valTypeNm>BY PERCENTAGE</valTypeNm>
                        </prodClsfnItm>
                        <prodTypeNm>Inline</prodTypeNm>
                    </prodClsfnDtl>
                    <prodPoDtl>
                        <originCountries>VN</originCountries>
                        <uom>PR</uom>
                    </prodPoDtl>
                    <seasonNm>Season 2020</seasonNm>
                    <silDesc>Sil description</silDesc>
                    <silNm>020</silNm>
                    <sportActyDesc>CASUAL/LEISURE</sportActyDesc>
                    <sportActyNm>29</sportActyNm>
                </prodCstmsHdr>
        </ns2:getProductClassificationUpdatesResponse>
    </S:Body>
</S:Envelope>') xmldoc;