SQL Server中将含子标签的XML转为单行结构表的T-SQL查询求助

你好！看起来你已经迈出了用T-SQL解析XML的第一步，现在需要把每个测试用例的所有步骤合并成同一行字符串对吧？我来帮你完善代码，给你两种可行的方案：

方法1：基于你现有OPENXML的改进方案 #

你原来的代码把steps作为XML类型返回，我们可以在WITH子句里直接用XQuery拼接步骤内容，不需要额外处理：

DECLARE @xml XML = '
<testcases>
 <testcase>
 <testcasename> first </testcasename>
 <MediaType> Voice </MediaType>
 <steps>
 <CallSteps>
 <Step>
 <StepNo>1</StepNo>
 <description> first step </description>
 <StepNo>2</StepNo>
 <description> second step </description>
 </Step>
 </CallSteps>
 </steps>
 </testcase>
 <testcase>
 <testcasename> second </testcasename>
 <MediaType> Chat </MediaType>
 <steps>
 <CallSteps>
 <Step>
 <StepNo>1</StepNo>
 <description> first step </description>
 <StepNo>2</StepNo>
 <description> second step </description>
 <StepNo>3</StepNo>
 <description> Third step </description>
 </Step>
 </CallSteps>
 </steps>
 </testcase>
 <testcase>
 <testcasename> Third </testcasename>
 <MediaType> Voice </MediaType>
 <steps>
 <CallSteps>
 <Step>
 <StepNo>1</StepNo>
 <description> first step </description>
 </Step>
 </CallSteps>
 </steps>
 </testcase>
</testcases>';

DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;

SELECT 
    LTRIM(RTRIM(testcasename)) AS testcasename,
    LTRIM(RTRIM(MediaType)) AS MediaType,
    -- 用XQuery循环拼接所有步骤为单行
    steps.value('
        for $s in (CallSteps/Step/StepNo, CallSteps/Step/description)
        let $pos := count(CallSteps/Step/StepNo[. << $s]) + 1
        return if ($s instance of element(StepNo)) then concat($s, ": ") else concat($s, "; ")
    ', 'NVARCHAR(MAX)') AS steps
FROM OPENXML(@idoc, '/testcases/testcase', 2)
WITH (
    testcasename NVARCHAR(10),
    MediaType NVARCHAR(10),
    steps XML
);

EXEC sp_xml_removedocument @idoc; -- 记得手动释放XML文档资源

这段代码会把每个StepNo和对应的description拼接成[序号]: [描述]; 的格式，直接返回单行的步骤字符串。

方法2：更简洁的XQuery原生解析（推荐） #

SQL Server支持直接对XML变量用XQuery查询，不需要依赖OPENXML，代码更简洁，也不用管理文档句柄：

DECLARE @xml XML = '
<testcases>
 <testcase>
 <testcasename> first </testcasename>
 <MediaType> Voice </MediaType>
 <steps>
 <CallSteps>
 <Step>
 <StepNo>1</StepNo>
 <description> first step </description>
 <StepNo>2</StepNo>
 <description> second step </description>
 </Step>
 </CallSteps>
 </steps>
 </testcase>
 <testcase>
 <testcasename> second </testcasename>
 <MediaType> Chat </MediaType>
 <steps>
 <CallSteps>
 <Step>
 <StepNo>1</StepNo>
 <description> first step </description>
 <StepNo>2</StepNo>
 <description> second step </description>
 <StepNo>3</StepNo>
 <description> Third step </description>
 </Step>
 </CallSteps>
 </steps>
 </testcase>
 <testcase>
 <testcasename> Third </testcasename>
 <MediaType> Voice </MediaType>
 <steps>
 <CallSteps>
 <Step>
 <StepNo>1</StepNo>
 <description> first step </description>
 </Step>
 </CallSteps>
 </steps>
 </testcase>
</testcases>';

SELECT
    LTRIM(RTRIM(tc.value('(testcasename/text())[1]', 'NVARCHAR(10)'))) AS testcasename,
    LTRIM(RTRIM(tc.value('(MediaType/text())[1]', 'NVARCHAR(10)'))) AS MediaType,
    -- 用string-join拼接所有步骤为单行
    tc.query('
        string-join(
            for $i in 1 to count(steps/CallSteps/Step/StepNo)
            return concat(
                steps/CallSteps/Step/StepNo[$i]/text(),
                ": ",
                normalize-space(steps/CallSteps/Step/description[$i]/text()),
                "; "
            ),
            ""
        )
    ').value('.', 'NVARCHAR(MAX)') AS steps
FROM @xml.nodes('/testcases/testcase') AS x(tc);

这个方法用nodes()拆分每个testcase节点，再通过string-join和循环把每一组步骤拼接起来，还加入了normalize-space()自动清理描述文本前后的空格，结果会更整洁。

补充说明 #

两种方法最终都会返回类似这样的结果：

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