无法在SQL中使用OPENXML查询XML文件 [英] Unable to Query XML file with OPENXML in SQL

问题描述

我正在尝试使用SQL Server 2014查询大量XML文件.我正在使用以下代码，但由于无法返回任何内容，因此我不确定语法有什么问题.我怀疑XML文件有些奇怪.

I am trying to query a good number of XML files using SQL Server 2014. I am using the below code and I am unsure what is wrong with the syntax because nothing is being returned. My suspicion is there is something strange with the XML file.

如果仅将XML文本的一部分直接放入查询文件中而不是在本地指向它，那么它似乎可以工作，但是我有很多文件，因此确实需要能够从本地源进行查询而无需手动操作文件.

If put in only parts of the XML text directly into the query file instead of pointing to it locally then it seems to work but I have a lot of files and really need to be able to query from a local source without manual manipulation of the files.

示例XML: https://s3.amazonaws.com/irs-表格990/201600349349300510_public.xml

我的代码:

DECLARE @x xml
SELECT @x = R
FROM OPENROWSET (BULK 'C:\Users\USER\990\Example.xml', SINGLE_BLOB) AS     ReturnData(R)

SELECT @x

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT * FROM OPENXML (@hdoc, '/Return/ReturnData/IRS990ScheduleHIRS990ScheduleH/FinancialAssistanceAtCostTyp',3)  
  WITH (FinancialAssistancePolicyInd  int   '../FinancialAssistancePolicyInd',  
        FPGReferenceDiscountedCareInd int   '../FPGReferenceDiscountedCareInd',
        PersonsServedCnt int,
        NetCommunityBenefitExpnsAmt int)  

EXEC sp_xml_removedocument @hdoc

先谢谢了.如果有更好的方法可以做到这一点，那么请告诉我，我是在SQL中使用XML的新手.

Thanks in advance. IF there is a better way to do this then please do let me know, I am new to working with XML in SQL.

推荐答案

有几个缺陷:

• FROM OPENXML已过时，不应再使用(存在罕见的例外)

• FROM OPENXML is outdated and should not be used any more (rare exceptions exist)

您的XML包含一个默认名称空间，必须声明该名称空间

your XML included a default namespace, which must be declared

您的XPath错误:/Return/ReturnData/IRS990ScheduleHIRS990ScheduleH/应该为/Return/ReturnData/IRS990ScheduleH/

your XPath is wrong: /Return/ReturnData/IRS990ScheduleHIRS990ScheduleH/ should be /Return/ReturnData/IRS990ScheduleH/

但是无论如何，您都应该转向现代的XQuery方法.像这样尝试:

But anyway you shoudl turn to modern XQuerymethods. Try it like this:

-这会将XML读入声明的变量.

--This will read the XML into a declared variable.

-注意:您的XML用utf-8声明，这可能导致特殊字符出现问题...

--attention Your XML is declared with utf-8, this might lead to problems with special characters...

DECLARE @x xml
SELECT @x = R
FROM OPENROWSET (BULK 'C:\Users\USER\990\Example.xml', SINGLE_BLOB) AS ReturnData(R);

-这是查询，首先声明名称空间，然后使用.nodes()和.value():

--This is the query, first declare the namespace(s), than use .nodes() and .value():

WITH XMLNAMESPACES(DEFAULT 'http://www.irs.gov/efile'
                          ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT ct.value('(FinancialAssistancePolicyInd)[1]','int') AS FinancialAssistancePolicyInd
      ,ct.value('(FPGReferenceDiscountedCareInd)[1]','int') AS FPGReferenceDiscountedCareInd
      ,ct.value('(FinancialAssistanceAtCostTyp/PersonsServedCnt)[1]','int') AS PersonsServedCnt
      ,ct.value('(FinancialAssistanceAtCostTyp/NetCommunityBenefitExpnsAmt)[1]','int') AS NetCommunityBenefitExpnsAmt
FROM @x.nodes('/Return/ReturnData/IRS990ScheduleH') AS A(ct)

这篇关于无法在SQL中使用OPENXML查询XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！