XML导入SQL数据库的三类技术问题咨询

我有一份需导入SQL的XML文件（内容如下），并使用了相应SQL代码（代码如下），现存在三类技术疑问：

1. 为何无法使用@ns2:En、@Type、@Weight，且需在这些节点后添加[1]？
2. 为何'(Weight/unit)[1]'语句无法生效？
3. 将OUTER APPLY改为CROSS APPLY后结果一致，对两者的适用场景存在疑问。

XML内容 #

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns4:Dataview1 xmlns="ux:no::ehe:v5:actual:aver" xmlns:ns4="ux:no:ehe:v5:move"  xmlns:ns2="ux:no:ehe:v5:centre" xmlns:ns5="ux:no:ehe:v5:tran">
  <ns4:Content versionCode="16000">
    <ns4:gen start="1961-07-01" end="1961-07-01">
    </ns4:gen>
      <Weight unit="KG">30.0000</Weight>
    <ns4:gen start="2017-09-19">
    </ns4:gen>
    <ns4:gen start="1961-07-02" end="2016-09-30">
      <ns5:translation>
        <ns2:En>Example Data English 234354</ns2:En>
      </ns5:translation>
      <Type>Circle</Type>
      <Weight unit="KG">10.0000</Weight>
    </ns4:gen>
    <ns4:gen start="2016-10-01" end="2017-09-18">
      <ns5:translation>
        <ns2:De>Beispieldaten Deutschland 23443</ns2:De>
        <ns2:En>Example Data English 23443</ns2:En>
      </ns5:translation>
      <Weight unit="KG">20.0000</Weight>
    </ns4:gen>
  </ns4:Content>
</ns4:Dataview1>

SQL代码 #

CREATE TABLE tbl (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    XmlColumn XML
);

INSERT INTO tbl(XmlColumn)
SELECT * FROM OPENROWSET(BULK N'C:\Data\demoset.xml', SINGLE_BLOB) AS x;

WITH XMLNAMESPACES(DEFAULT  'ux:no::ehe:v5:actual:aver',
                            'ux:no:ehe:v5:tran' as ns5,
                            'ux:no:ehe:v5:move' AS ns4,
                            'ux:no:ehe:v5:cat:fill' as ns3,
                            'ux:no:ehe:v5:centre' as ns2)

SELECT   c.value('@versionCode', 'VARCHAR(100)') as versionCode,
         x.value('@start', 'DATE') as Start_date,
         x.value('@end', 'DATE') as End_date,
         x.value('(ns5:translation/ns2:En)[1]', 'VARCHAR(100)') as Trans_EN,
         x.value('Type[1]', 'VARCHAR(100)') as Typed,
         x.value('(Weight/unit)[1]', 'VARCHAR(100)') as Unitdata,
         x.value('Weight[1]', 'VARCHAR(100)') as Weightdata
INTO dbo.testtbl
FROM tbl
    CROSS APPLY XmlColumn.nodes('/ns4:Dataview1/ns4:Content') AS t1(c)
    OUTER APPLY t1.c.nodes('ns4:gen') AS t2(x);

疑问解答 #

1. 为何无法使用@ns2:En、@Type、@Weight，且需添加[1]？ #

• @符号在XQuery中是用于选取属性的标记，而ns2:En、Type、Weight都是XML中的元素节点，并非属性，所以不能加@，否则会找不到对应节点。
• 添加[1]是因为SQL的value()方法强制要求返回单个值。即使当前XML中每个路径仅对应一个节点，value()方法也需要明确指定取第一个匹配节点（用索引[1]），避免因潜在多值匹配导致的报错。

2. 为何'(Weight/unit)[1]'语句无法生效？ #

unit是Weight元素的属性，不是子元素。正确的XQuery语法应该用@unit来选取属性，所以正确写法是(Weight/@unit)[1]。原写法是查找Weight下名为unit的子元素，与XML结构不符，因此无法获取数据。

3. OUTER APPLY和CROSS APPLY的适用场景 #

• CROSS APPLY等价于内连接：仅当右侧nodes()方法返回匹配结果时，才会生成对应行。如果某个ns4:Content下没有ns4:gen节点，这部分数据会被过滤。
• OUTER APPLY等价于左外连接：无论右侧nodes()是否返回结果，左侧的行都会保留，右侧无匹配的列会显示为NULL。

你这次结果一致，是因为当前XML的ns4:Content下确实存在多个ns4:gen节点，没有出现右侧无匹配的情况。若XML中存在不含gen节点的Content，用CROSS APPLY会丢失该行数据，而OUTER APPLY会保留该行，对应列值为NULL。

内容的提问来源于stack exchange，提问作者Zayfaya83