无需CROSS APPLY与WHERE，解析SQL Server指定XML的方案

示例XML结构 #

假设你的XML数据结构如下：

<SurveyQLADetails>
  <QlaDetail>
    <QuestionID>1</QuestionID>
    <QuestionText>您的年龄？</QuestionText>
    <Answer>25</Answer>
  </QlaDetail>
  <QlaDetail>
    <QuestionID>2</QuestionID>
    <QuestionText>您的职业？</QuestionText>
    <Answer>程序员</Answer>
  </QlaDetail>
</SurveyQLADetails>

原CROSS APPLY实现（参考） #

你之前可能用的是这类写法：

SELECT 
    x.value('(QuestionID/text())[1]', 'INT') AS QuestionID,
    x.value('(QuestionText/text())[1]', 'NVARCHAR(100)') AS QuestionText,
    x.value('(Answer/text())[1]', 'NVARCHAR(100)') AS Answer
FROM 
    YourTable
CROSS APPLY 
    YourXmlColumn.nodes('/SurveyQLADetails/QlaDetail') AS T(x)

方案1：使用OPENXML解析（完全无需CROSS APPLY/WHERE） #

这种方式通过系统存储过程处理XML，直接映射为表格数据：

-- 若XML来自表列，可替换为SELECT @XmlData = YourXmlColumn FROM YourTable
DECLARE @XmlData XML = '<SurveyQLADetails>
  <QlaDetail>
    <QuestionID>1</QuestionID>
    <QuestionText>您的年龄？</QuestionText>
    <Answer>25</Answer>
  </QlaDetail>
  <QlaDetail>
    <QuestionID>2</QuestionID>
    <QuestionText>您的职业？</QuestionText>
    <Answer>程序员</Answer>
  </QlaDetail>
</SurveyQLADetails>'

DECLARE @XmlHandle INT
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @XmlData

SELECT 
    QuestionID,
    QuestionText,
    Answer
FROM 
    OPENXML(@XmlHandle, '/SurveyQLADetails/QlaDetail', 2)
    WITH (
        QuestionID INT 'QuestionID',
        QuestionText NVARCHAR(100) 'QuestionText',
        Answer NVARCHAR(100) 'Answer'
    )

EXEC sp_xml_removedocument @XmlHandle

方案2：直接调用.nodes（单XML变量场景无需CROSS APPLY） #

如果是单独的XML变量而非表中列，可直接从nodes结果集查询，无需关联操作：

DECLARE @XmlData XML = '<SurveyQLADetails>
  <QlaDetail>
    <QuestionID>1</QuestionID>
    <QuestionText>您的年龄？</QuestionText>
    <Answer>25</Answer>
  </QlaDetail>
  <QlaDetail>
    <QuestionID>2</QuestionID>
    <QuestionText>您的职业？</QuestionText>
    <Answer>程序员</Answer>
  </QlaDetail>
</SurveyQLADetails>'

SELECT 
    x.value('(QuestionID/text())[1]', 'INT') AS QuestionID,
    x.value('(QuestionText/text())[1]', 'NVARCHAR(100)') AS QuestionText,
    x.value('(Answer/text())[1]', 'NVARCHAR(100)') AS Answer
FROM 
    @XmlData.nodes('/SurveyQLADetails/QlaDetail') AS T(x)

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