我正在尝试使用SQL Server 2014查询大量XML文件。我正在使用下面的代码,我不确定语法有什么问题,因为没有返回任何内容。我怀疑XML文件有些奇怪。
如果只将部分 XML 文本直接放入查询文件而不是指向本地,那么它似乎可以工作,但我有很多文件,确实需要能够从本地源查询而无需手动操作文件。
示例 XML:https://s3.amazonaws.com/irs-form-990/201600349349300510_public.xml
我的代码:
DECLARE @x xml
SELECT @x = R
FROM OPENROWSET (BULK 'C:UsersUSER990Example.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的新手。
有几个缺陷:
-
FROM OPENXML已经过时,不应再使用(存在极少数例外)
-
您的 XML 包含一个默认命名空间,必须声明该命名空间
-
你的XPath是错误的:
/Return/ReturnData/IRS990ScheduleHIRS990ScheduleH/
应该/Return/ReturnData/IRS990ScheduleH/
但无论如何,您应该转向现代XQuery
方法。像这样尝试:
-这会将 XML 读入声明的变量中。
--注意 您的XML是用utf-8
声明的,这可能会导致特殊字符出现问题...
DECLARE @x xml
SELECT @x = R
FROM OPENROWSET (BULK 'C:UsersUSER990Example.xml', SINGLE_BLOB) AS ReturnData(R);
--这是查询,首先声明命名空间,然后使用.nodes()
和.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)