如何在 OUTER APPLY 查询中将 varchar 转换为 XML

问题描述

给定以下示例 SQL，如果 XML 本身的两个 varchar(max) 声明和 @t 表声明实际上是 xml 字段，它将完美地工作类型.

Given the following sample SQL, it would work perfectly IF the two varchar(max) declarations for both the XML itself and the @t table declaration were actually xml field types.

DECLARE @x varchar(max) = '<?xml version="1.0" encoding="UTF-8"?>
<AuditTrail>
   <Action />
   <ActionDetail />
   <ChangesXML>
      <Details>
         <Object ObjectType="Data.Review_Extension" AuditType="Modified" FriendlyName="Review">
            <ObjectKeys>
               <ReviewExtID>21482283</ReviewExtID>
            </ObjectKeys>
            <Properties>
               <Property name="Document Type 01" FieldName="Document_Type_01" TemplateFieldID="644140" ReviewExtensionID="214822182" PropertyType="System.String">
                  <OldValue />
                  <NewValue><![CDATA[1145]]></NewValue>
               </Property>
               <Property name="Document Type 02" FieldName="Document_Type_02" TemplateFieldID="644141" ReviewExtensionID="21482283" PropertyType="System.String">
                  <OldValue />
                  <NewValue><![CDATA[123]]></NewValue>
               </Property>
            </Properties>
         </Object>
      </Details>
   </ChangesXML>
</AuditTrail>'

DECLARE @t TABLE (userid INT, [xml] varchar(max))
INSERT @t VALUES(1, @x)

SELECT t.userid, r.z.value('@FieldName', 'nvarchar(MAX)')
FROM @t t
OUTER APPLY t.xml.nodes('//Property') as r(z) 

如何修改此 SQL 底部的 SELECT 查询以将 [xml] 字段转换为 XML 字段类型，以便我可以使用 t.xml.nodes?

推荐答案

只需先使用一个 OUTER APPLY 来强制一个 CAST :

Just use one OUTER APPLY first to force a CAST:

SELECT t.userid, r.z.value('@FieldName', 'nvarchar(MAX)')
FROM @t t
OUTER APPLY (SELECT CAST(t.xml as xml) as realxml) s
OUTER APPLY s.realxml.nodes('//Property') as r(z)