我应该如何加载包含注释和空格的XML文件，然后在根元素上使用XMLGET，我无法获得子元素 [英] How should I load XML file which has comments and spaces in them and then using XMLGET on the root element, I'm not able to get the child elements

问题描述

(代表Snowflake用户提交)

---

使用：

<clinical_study>
 <!-- This xml conforms to an XML Schema at:
  https://clinicaltrials.gov/ct2/html/images/info/public.xsd -->
 <required_header>
  <download_date>ClinicalTrials.gov processed this data on September 13, 2019</download_date>
  <link_text>Link to the current ClinicalTrials.gov record.</link_text>
  <url>https://clinicaltrials.gov/show/NCT00010010</url>
 </required_header>
 <id_info>
  <org_study_id>CDR0000068431</org_study_id>
  <secondary_id>NYU-0004</secondary_id>
  <secondary_id>P-UPJOHN-NYU-0004</secondary_id>
  <secondary_id>NCI-G00-1906</seco

我得到的是NULL，而不是根元素内容。我已经阅读了Snowflake文档中的"How to Easily Load and Query XML Data with Snowflake Part 2"，现在使用的是：

SELECT XMLGET(src_xml, 'clinical_study'):"$",
*
FROM STG_XML
;

.但它给了我NULL，因为我正在尝试使用上面的SQL获取根元素的内容。

---

是否有任何想法、建议和/或解决办法？

xml

正如Mike Walton所说，推荐答案是不完整的(这会阻止其他人容易地重现OP所询问的NULL)。如果我们关闭打开的XML元素，XMLGET中的NULL的问题是"Clinic_Study"是根节点.XMLGET检索根节点内的元素。为了返回根节点的内容，您可以使用表达式：

src_xml:"$" AS clinical_study_contents

下面是一个简单的测试工具来演示这一点，以及XMLGET的有效用法(提取"id_info"元素的内容)：

WITH STG_XML AS (
  SELECT PARSE_XML($1) AS src_xml
    FROM VALUES
           ($$
<clinical_study>
 <!-- This xml conforms to an XML Schema at:
  https://clinicaltrials.gov/ct2/html/images/info/public.xsd -->
 <required_header>
  <download_date>ClinicalTrials.gov processed this data on September 13, 2019</download_date>
  <link_text>Link to the current ClinicalTrials.gov record.</link_text>
  <url>https://clinicaltrials.gov/show/NCT00010010</url>
 </required_header>
 <id_info>
  <org_study_id>CDR0000068431</org_study_id>
  <secondary_id>NYU-0004</secondary_id>
  <secondary_id>P-UPJOHN-NYU-0004</secondary_id>
  <secondary_id>NCI-G00-1906</secondary_id>
 </id_info>
</clinical_study>
$$)
)
SELECT src_xml:"$" AS clinical_study_contents
      ,XMLGET(src_xml, 'id_info') as id_info_element
      ,*
  FROM STG_XML
;

这篇关于我应该如何加载包含注释和空格的XML文件，然后在根元素上使用XMLGET，我无法获得子元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！