SQL Server导入制裁XML名单至数据表时查询返回NULL值问题

• XPath路径与实际XML结构不匹配：代码中写的nodes路径错误假设<remark>/<regulation>/<subjectType>/<nameAlias>/<citizenship>是多层嵌套的父子关系，但实际这些节点都是<sanctionEntity>下的平级兄弟节点，多层嵌套的路径根本匹配不到任何有效节点。另外XML语法大小写敏感，代码中写的subjecttype和实际节点名subjectType大小写不一致，也会导致路径匹配失效。
• 父节点回溯层级错误：如果以<citizenship>为锚点，它的直接父节点就是<sanctionEntity>，只需要向上回溯1层（../）就能到实体层级，代码中写的../../会向上回溯2层到根节点<export>，该节点下没有<nameAlias>子节点，自然取不到值。
• 属性取值语法错误：firstName/lastName/wholeName都是<nameAlias>标签的属性，不是子节点。代码中写的nameAlias/firstName是查找nameAlias下名为firstName的子元素，不符合XML结构，属性取值需要加@前缀。

直接以<nameAlias>节点为遍历锚点，避免不必要的父节点回溯，属性取值加@前缀，路径严格匹配XML实际层级和大小写，参考代码如下：

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn  
FROM OPENROWSET(BULK 'C:\echo\20220617.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES (DEFAULT 'http://eu.europa.ec/fpi/fsd/export')
SELECT 
    euReferenceNumber = nameAlias.value('../@euReferenceNumber', 'varchar(20)'),
    firstName = nameAlias.value('@firstName', 'varchar(50)'),
    middleName = nameAlias.value('@middleName', 'varchar(50)'),
    lastName = nameAlias.value('@lastName', 'varchar(100)'),
    wholeName = nameAlias.value('@wholeName', 'varchar(200)'),
    gender = nameAlias.value('@gender', 'char(1)'),
    citizenshipCountry = nameAlias.value('../citizenship[1]/@countryDescription', 'varchar(50)'),
    citizenshipCountryCode = nameAlias.value('../citizenship[1]/@countryIso2Code', 'char(2)')
FROM
    -- 路径严格匹配XML结构：export -> sanctionEntity -> nameAlias
    @XmlFile.nodes('//export/sanctionEntity/nameAlias') AS aliasTbl(nameAlias)

运行上述代码可以直接提取到所有制裁实体的别名信息，同时关联获取实体编号、国籍等关联属性，不会再返回NULL值。

如果确实需要以citizenship为锚点遍历，只需要修正回溯层级和属性取值语法即可，取值写法为resource.value('../nameAlias[1]/@firstName', 'varchar(50)')，nodes路径修正为'//export/sanctionEntity/citizenship'，但这种写法会因为一个实体对应多个别名、多个国籍出现重复数据，不推荐使用。

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