sql-server - SQL 中的 XML 命名空间混淆
问题描述
我有一个 XML 文件,我需要对其进行一些修改。
我可以在 SQL 中很好地加载文件,但是当我尝试读取一些节点时,我什么也没有返回……也没有错误。我究竟做错了什么?
我的 XML 的开头是这样的:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<batch_request xmlns="http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd batchsubmission- schema.xsd">
<raise_eligibility_document>
<ed:Certificate xmlns="http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd" xmlns:ed="http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd">
<ed:Identifiers>
<ed:CertificateID>26920</ed:CertificateID>
<ed:TemplateID>ED1</ed:TemplateID>
</ed:Identifiers>
<ed:DepartureDate>2021-03-10</ed:DepartureDate>
<ed:Parties>
<ed:ConsignorID>XX345</ed:ConsignorID>
<ed:ConsigneeID>FLIGHT1</ed:ConsigneeID>
</ed:Parties>
这是我加载和读取此文件的 SQL 语句以及我返回节点的尝试。
Declare @xmlFile VARCHAR(30) = 'TESTING'
if OBJECT_ID('tempdb..#XmlImportTest') is not null
drop table #XmlImportTest
CREATE TABLE #XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
);
declare @xmlpath varchar(200) = 'Location of file'
declare @xmlFileName varchar(200) = @xmlpath+@xmlFile+'.xml'
--– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('INSERT INTO #XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
select @xml = (SELECT xml_data from #XmlImportTest)
--select @xml
;with xmlnamespaces (
'http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd' as multiple,
'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd' as submission
)
select
header.value(N'(submission:Identifiers/submission:CertificateID/text())[1]', N'nvarchar(20)') as ecert_number
from @xml.nodes('/submission:Certificate') as ci(header)
这不返回任何内容。
我也试过这个:
from
@xml.nodes('/submission:batch_request/submission:raise_eligibility_document/submission:Certificate') as ci(header)
我还尝试将以下内容用于提交名称空间
;with xmlnamespaces (
'http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd' as multiple,
--'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd' as submission ,
'http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd' as submission
)
我仍然一无所有..请问我哪里出了问题?
解决方案
这是一个完整的再现如何正确地做到这一点。
需要提及的要点:
- 只有两个名称空间与提供的 XML 摘录相关。其中之一是默认命名空间,因此不需要它的前缀。第二个命名空间需要“ed”前缀。
.nodes()
现在,在两个位置(即.value()
XQuery 方法)中都有适当的 XPath 表达式。
SQL
DECLARE @xml XML =
N'<batch_request xmlns="http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd batchsubmission- schema.xsd">
<raise_eligibility_document>
<ed:Certificate xmlns="http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd"
xmlns:ed="http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd">
<ed:Identifiers>
<ed:CertificateID>26920</ed:CertificateID>
<ed:TemplateID>ED1</ed:TemplateID>
</ed:Identifiers>
<ed:DepartureDate>2021-03-10</ed:DepartureDate>
<ed:Parties>
<ed:ConsignorID>XX345</ed:ConsignorID>
<ed:ConsigneeID>FLIGHT1</ed:ConsigneeID>
</ed:Parties>
</ed:Certificate>
</raise_eligibility_document>
</batch_request>';
WITH XMLNAMESPACES (DEFAULT 'http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd'
, 'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd' AS ed)
SELECT c.value('(ed:Identifiers/ed:CertificateID/text())[1]', 'VARCHAR(30)') AS CertificateID
, c.value('(ed:Identifiers/ed:TemplateID/text())[1]', 'VARCHAR(30)') AS TemplateID
, c.value('(ed:DepartureDate/text())[1]', 'DATE') AS DepartureDate
, c.value('(ed:Parties/ed:ConsignorID/text())[1]', 'VARCHAR(30)') AS ConsignorID
, c.value('(ed:Parties/ed:ConsigneeID/text())[1]', 'VARCHAR(30)') AS ConsigneeID
FROM @xml.nodes('/batch_request/raise_eligibility_document/ed:Certificate') AS t(c);
输出
+---------------+------------+---------------+-------------+-------------+
| CertificateID | TemplateID | DepartureDate | ConsignorID | ConsigneeID |
+---------------+------------+---------------+-------------+-------------+
| 26920 | ED1 | 2021-03-10 | XX345 | FLIGHT1 |
+---------------+------------+---------------+-------------+-------------+
推荐阅读
- php - 如何在后端获取 10 条记录,然后使用 Laravel DataTables 加载每条记录?
- angular - mat-progress-bar 问题,BehaviorSubject 不工作
- php - 为什么我得到这个无效的解析?
- html - 符号标签中的 svg 在底部占用空格
- javascript - 使用js在同一张表上发布2个值
- javascript - Mongoose 填充数组字段的字段
- sql - SQL 统计提及次数
- laravel - 一个查询中的雄辩模型关系
- c++ - 如何使用ANSI返回当前窗口之外的上一个位置?(C++)
- r - ggplot (R) 中与已编辑实验室和 ylab 组合的散点图