用于从雪花中的XML数据检索标记(子节点)内的值的SQL查询 [英] SQL Query to retrieve values inside tags(child nodes) from a XML data in SnowFlake

问题描述

我有一个包含以下数据的XML(已编辑)文件：

<dept dept_id="1" dept_name="Marketing">
     <progress>1</progress>
    <employee empname="a">
         <end> 1 </end>
        <address addr1="123 abc">
        ...
        </address>
    </employee>
</dept>
<dept dept_id="2" dept_name="Sales">
    <progress>1</progress>
    <employee empname="b">
        <end> 1 </end>
        <address addr1="456 cde">
        ...
        </address>
    </employee>
</dept>

我将此文件放入AWS S3中，然后使用"复制到"将此数据传输到变量列的Snowflake中的外部表中。如下所示：

  copy into DB.AWS_S3_STAGE_SCHEMA.test_XML_copy
  from @AWS_S3_LANDING/websiteXML/Test_xml.xml
  FILE_FORMAT = ( TYPE =  XML STRIP_OUTER_ELEMENT = TRUE  )  ;

现在，我可以查询该表中的数据，并使用以下查询(我从Snowflake文档中获得语法并使用它)检索‘Marketing’和‘Sales’之类的数据，它们位于标记内。)：

SELECT
    GET(xmldata, '@dept_id')::integer as dept_id,
    GET(xmldata, '@dept_name')::string as dept_name
FROM test_XML_copy;

但是，我不能查询子节点中的标记内的数据。例如：我需要像‘a’和‘123abc’这样的数据。 如果有人能在这个问题上帮我，我将不胜感激。

xml

组合使用XMLGET和GET函数遍历推荐答案文档中的嵌套对象。

前者帮助获取在当前标记下找到的整个Tag对象，而后者允许查询当前标记内的属性和常规值。

SELECT
    -- <dept> (root)
    GET(xmldata, '@dept_id')::integer as dept_id,
    GET(xmldata, '@dept_name')::string as dept_name,
    -- <dept>.<employee>
    GET(XMLGET(xmldata, 'employee'), '@empname')::string as employee_name,
    -- <dept>.<employee>.<address>
    GET(XMLGET(XMLGET(xmldata, 'employee'), 'address'), '@addr1')::string as address_1
FROM test_XML_copy;

哪些应产生：

+---------+-----------+---------------+-----------+                             
| DEPT_ID | DEPT_NAME | EMPLOYEE_NAME | ADDRESS_1 |
|---------+-----------+---------------+-----------|
|       1 | Marketing | a             | 123 abc   |
|       2 | Sales     | b             | 456 def   |
| ...     | ...       | ...           | ...       |
+---------+-----------+---------------+-----------+

您的示例数据没有显示重复子标记，但是如果它们确实重复(如每个dept中有多个employee)then FLATTEN can first be used to produce one employee per row，则可以重新应用上面的方法。或者，如果它是固定形式的标签结构，并且它们始终是有序的，您可以使用XMLGET中的实例编号来指向每个实例(隐式默认值为0，第一个对象)。

将文档分解为每个员工和每个地址的一行内部标记的示例：

SELECT
xmldata:"@dept_id"::integer as dept_id,
xmldata:"@dept_name"::string as dept_name,
emp.value:"@empname"::string as employee_name,
addr.value:"@addr1"::string as address_1
FROM
test_XML_copy,
LATERAL FLATTEN(xmldata:"$") emp,
LATERAL FLATTEN(emp.value:"$") addr
WHERE emp.value:"@" = 'employee' AND addr.value:"@" = 'address';

(对于OP的问题中提供的示例，这将产生与上面类似的结果)

注意：您也可以使用path syntax与$和@字符来导航结构，而不是嵌套函数，但这些操作依赖于输入数据结构的严格顺序：

-- See outer / inner structures in 'JSON' form
SELECT
    xmldata:"@" dept_tag,
    xmldata:"$" dept_tag_contents
FROM test_XML_copy;

-- Sample equivalent query using path expressions, relying on ordering:
SELECT
    xmldata:"@dept_id",
    xmldata:"@dept_name",
    xmldata:"$"[1]."@empname",
    xmldata:"$"[1]."$"[1]."@addr1"
FROM test_XML_copy;

这篇关于用于从雪花中的XML数据检索标记(子节点)内的值的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！