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

How to cast varchar to XML in an OUTER APPLY query(如何在 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) 

这篇关于如何在 OUTER APPLY 查询中将 varchar 转换为 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！