Select XML elements into table rows in SQL Server

Question

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

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

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?

Kirill Polishchuk · Accepted Answer · 2015-05-27 00:44:07Z

6

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

answered May 27, 2015 at 0:44

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Over a year ago

This is pretty much what I'm looking for. Only question, where in this query can I call CAST() on the [xml] node? In my SQL schema this is actually an ntext field.

Scott Over a year ago

Answered this question here: stackoverflow.com/questions/30484279/…

Collectives™ on Stack Overflow

Select XML elements into table rows in SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related