带外部列的exec sp_xml_preparedocument

Question

我有一张桌子

declare  @temp_xml table
(
    question_id int,
    question_xml xml
)

insert into @temp_xml(question_id, question_xml)
values(51, '<qst qodm="Horizontal" oprm="New Line" oph="25" opw="200" stext=""   filepath="" peqid="-1" peaid="-1" pipeqtype="1" rad="False"><cps><qvs qvid="V1" qvt="Required" qvem="Please choose at least one answer." /><qvs qvid="V15" qvt="UK Phone Number " qvem="Is not a correct phone format." /></cps><branch><![CDATA[]]></branch></qst>'
      ),
      (52,
   '<qst qodm="Horizontal" oprm="New Line" oph="25" opw="200" stext="" filepath="" peqid="-1" peaid="-1" pipeqtype="1" rad="False"><cps><qvs qvid="V1" qvt="Required" qvem="Please choose at least one answer." /><qvs qvid="V5" qvt="US/Canada  Phone Number " qvem="Is not a correct phone format." /></cps><branch><![CDATA[]]></branch></qst>'
      )

select * from @temp_xml

现在我想这样展示。

   Question_id  qvt                       qvem
   51,          Required,                 Please choose at least one answer.
   51,          UK Phone Number,          Is not a correct phone format.
   52,          Required,                 Please choose at least one answer.
   52,          US/Canada  Phone Number,  Is not a correct phone format.

有谁能帮我吗？

谢谢。

Answer 1

在SQL Server2005和更高版本中，我会完全避免使用旧的openxml API，而使用新的XQuery样式。

在您的示例中，这将类似于：

DECLARE @Temp_XML TABLE (question_id int, question_xml xml)

insert into @Temp_XML(question_id, question_xml)
values(51, 
'<qst qodm="Horizontal" oprm="New Line" oph="25" opw="200" stext="" filepath="" peqid="-1" peaid="-1" pipeqtype="1" rad="False">
    <cps>
        <qvs qvid="V1" qvt="Required" qvem="Please choose at least one answer." />
        <qvs qvid="V15" qvt="UK Phone Number " qvem="Is not a correct phone format." />
    </cps>
    <branch><![CDATA[]]></branch>
</qst>'),
      (52,
'<qst qodm="Horizontal" oprm="New Line" oph="25" opw="200" stext="" filepath="" peqid="-1" peaid="-1" pipeqtype="1" rad="False">
    <cps>
        <qvs qvid="V1" qvt="Required" qvem="Please choose at least one answer." />
        <qvs qvid="V5" qvt="US/Canada  Phone Number " qvem="Is not a correct phone format." />
    </cps>
    <branch><![CDATA[]]></branch>
</qst>')

SELECT
    question_id,
    CPS.QVS.value('(@qvid)[1]', 'varchar(50)') AS 'QVID',
    CPS.QVS.value('(@qvt)[1]', 'varchar(50)') AS 'QuestionValidationType',
    CPS.QVS.value('(@qvem)[1]', 'varchar(50)') AS 'QuestionValidationMessage'
FROM  
    @Temp_xml
CROSS APPLY
    question_xml.nodes('/qst/cps/qvs') AS CPS(QVS)

这将产生如下输出(我包含了"QVID“列，只是为了显示实际选择的内容)：

question_id QVID  QuestionValidationType     QuestionValidationMessage
51           V1     Required                 Please choose at least one answer.
51           V15    UK Phone Number          Is not a correct phone format.
52           V1     Required                 Please choose at least one answer.
52           V5     US/Canada Phone Number   Is not a correct phone format.