SQL Server读取XML生成表返回NULL值问题求助

尝试在SQL Server中解析从MOEX获取的债券历史数据XML，XML结构如下：

<document>
    <data>
        <metadata>
            ...
        </metadata>
        <rows>
            <row TRADEDATE="2023-04-17" SHORTNAME="ВТБРКС01" SECID="RU000A1032P1" YIELDCLOSE = "12.93"  LEGALCLOSEPRICE="91.33" VOLUME="70824"/>
            <row .../>
            ...
        </rows>
    </data>
</document>

运行以下SQL代码后，返回的所有字段均为NULL：

USE MoexTest
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM history

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT secid, shortname, legalcloseprice, yieldclose, volume
FROM OPENXML(@hDoc, 'document/data/rows/row')
WITH 
(
secid [nvarchar](50) '@secid',
shortname [nvarchar] (10) '@shortname',
legalcloseprice [float] '@legalcloseprice',
yieldclose [float] '@yieldclose',
volume [integer] '@volume'
)

EXEC sp_xml_removedocument @hDoc
GO

实际返回结果：

期望返回结果：

问题原因 #

SQL Server的OPENXML对XML属性名大小写敏感，你在WITH子句中使用的是小写属性名（如@secid），但XML中的属性是大写形式（如SECID），导致无法匹配到对应值，所以返回NULL。

解决方法 #

方法1：修正OPENXML的属性名大小写

将WITH子句中的属性名改为与XML中一致的大写形式：

USE MoexTest
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM history

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT secid, shortname, legalcloseprice, yieldclose, volume
FROM OPENXML(@hDoc, 'document/data/rows/row')
WITH 
(
secid [nvarchar](50) '@SECID',
shortname [nvarchar](10) '@SHORTNAME',
legalcloseprice [float] '@LEGALCLOSEPRICE',
yieldclose [float] '@YIELDCLOSE',
volume [integer] '@VOLUME'
)

EXEC sp_xml_removedocument @hDoc
GO

方法2：使用SQL Server原生XML方法（推荐）

相比旧的OPENXML，SQL Server原生XML方法更简洁且无内存泄漏风险，直接用.value()提取属性：

USE MoexTest
GO

SELECT 
    x.value('@SECID', 'nvarchar(50)') AS secid,
    x.value('@SHORTNAME', 'nvarchar(10)') AS shortname,
    x.value('@LEGALCLOSEPRICE', 'float') AS legalcloseprice,
    x.value('@YIELDCLOSE', 'float') AS yieldclose,
    x.value('@VOLUME', 'integer') AS volume
FROM history
CROSS APPLY XMLData.nodes('/document/data/rows/row') AS t(x)
GO

说明 #

• 方法2无需调用sp_xml_preparedocument和sp_xml_removedocument，是SQL Server处理XML的推荐方案。
• 确保history表中的XMLData字段类型为XML，若为字符串类型需先转换为XML格式。

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