在SQL Server中选择XML元素到表行。

I have XML like the following in a single row within a table (there are many rows within the table):

我在一个表的一行中有如下XML(表中有很多行):

<?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>

I need to write a query (in SQL Server 2008) that will, for each row in my source table, output a row for EACH Property element in the XML. So if I queried on the record above, I would get the following result set:

我需要编写一个查询(在SQL Server 2008中)，它将针对源表中的每一行，为XML中的每个属性元素输出一行。因此，如果我在上面的记录上查询，我会得到以下结果集:

UserId    Timestamp               PropertyName
-------------------------------------------------
1         1-1-2011 00:11:22:11   Document_Type_01
2         1-1-2011 00:11:22:11   Document_Type_02

My source table looks like so:

我的源表是这样的:

UserId    Timestamp               XML
--------------------------------------
1         1-1-2011 00:11:22:11   <XML>
2         4-1-2011 00:22:33:22   <XML>
3         4-2-2011 00:14:33:22   <XML>

My first attempt at this looks like so:

我的第一次尝试是这样的:

SELECT  UserId, Timestamp,
      CAST(AuditXml AS XML).value('(/AuditTrail/ChangesXML/Details/Object/Properties/Property/@FieldName)[1]', 'varchar(50)')  AS PropertyName
FROM  History order by timestamp desc

Obviously this only works if there's one property element and only returns one row per record in the source table. How can I write this query such that it would return the result set I'm looking for?

显然，这只适用于只有一个属性元素且在源表中每个记录只返回一行的情况。如何编写这个查询，使其返回我正在查找的结果集?

1 个解决方案

#1

Have a look at OUTER APPLY operator and consider this example:

看一下外部应用算子，并考虑这个例子:

DECLARE @x XML = '<?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] XML)
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)

Hope it helps

希望它能帮助

#1