如何在SQL Server中从XML数据提取嵌套对象的完整列表

问题原因 #

你当前的写法存在两个问题：

• 只对顶层的person节点做了行拆分，没有对嵌套的body_part子节点做二次拆分
• XPath表达式里的[1]限定了只取body_part节点集合的第一个元素

解决方法 #

使用CROSS APPLY关联第二层nodes()方法，将目标person下的所有body_part节点拆分为独立行，直接读取每个节点的文本值即可，修改后的代码如下：

DECLARE @xml XML;  
SET @xml = '<persons>
    <person>
        <person_name>
            <firstName>Jon</firstName>
            <lastName>Johnson</lastName>
        </person_name>
        <person_data>
            <number>1</number>
        </person_data>
        <body_parts>
            <body_part>Head</body_part>
            <body_part>Leg</body_part>
            <body_part>Nose</body_part>
        </body_parts>
    </person>
    <person>
        <person_name>
            <firstName>Kathy</firstName>
            <lastName>Carter</lastName>
        </person_name>
        <person_data>
            <number>2</number>
        </person_data>
        <body_parts>
            <body_part>Head</body_part>
            <body_part>Palm</body_part>
            <body_part>Eye</body_part>
        </body_parts>
    </person>
    <person>
        <person_name>
            <firstName>Bob</firstName>
            <lastName>Burns</lastName>
        </person_name>
        <person_data>
            <number>3</number>
        </person_data>
        <body_parts>
            <body_part>Leg</body_part>
        </body_parts>
    </person>
</persons>';  

SELECT 
    p.p.value('(./person_name/firstName/text())[1]','varchar(20)') AS firstName,
    p.p.value('(./person_name/lastName/text())[1]','varchar(20)') AS lastName,
    bp.bp.value('text()[1]','varchar(20)') AS X
FROM @XML.nodes('persons/person') p(p)
-- 拆分当前person下的所有body_part节点为独立行
CROSS APPLY p.p.nodes('./body_parts/body_part') bp(bp)
WHERE p.p.value('(./person_data/number/text())[1]','int') = 2;

执行结果 #

firstName  lastName  X
---------- --------- -----
Kathy      Carter    Head
Kathy      Carter    Palm
Kathy      Carter    Eye

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