How to extract an XML Attribute from an XML varchar string using TSQL in SQL Server 2008

Question

I have a varchar column with a XML string like the one below. I need to extract the data within the "string" element of <void property="request">

In this example, here is the value I need to extract:

This is the data I need to extract right here.  Testing 123, Testing 1234

Any help would be greatly appreciated.

<?xml version="1.0" encoding="UTF-8"?> 
<java version="1.6.0_20" class="java.beans.XMLDecoder"> 
 <object class="java.util.HashMap"> 
  <void method="put"> 
   <string>data</string> 
   <object class="com.globo.abc.process.eb.data.ManualProcessData"> 
    <void property="caseId"> 
     <long>8406</long> 
    </void> 
    <void property="clientId"> 
     <long>25020</long> 
    </void> 
    <void property="context"> 
     <object class="java.util.HashMap"> 
      <void method="put"> 
       <string>TARGET_GROUP</string> 
       <long>102131</long> 
      </void> 
      <void method="put"> 
       <string>TYPE</string> 
       <string>TICKLER</string> 
      </void> 
     </object> 
    </void> 
    <void property="groupStepDefinitionId"> 
     <long>10274</long> 
    </void> 
    <void property="owner"> 
     <string>2256</string> 
    </void> 
    <void property="request"> 
     <string>This is the data I need to extract right here.  Testing 123, Testing 1234</string> 
    </void> 
    <void property="stepDefinitionName"> 
     <string>Manual Task</string> 
    </void> 
   </object> 
  </void> 
 </object> 
 </java>

With tweezers Mono Hijo

terary
– terary

2015-01-15 04:40:49 +00:00
Commented Jan 15, 2015 at 4:40 — terary
– terary, Commented Jan 15, 2015 at 4:40

Community · Accepted Answer · 2017-05-23 12:06:24Z

0

SQL Server or any database scripting language has an odd notion of 'collection' of data, they're called tables. In order to iterate over a collection of data you'll need to use a cursor and pick through the string. blah blah blah.

If you are trying to pull out a single known token then using charindex, substr, etc.

If you have multiple tokens you'll have to do the cursor and charindex, etc.

Or change or you datatype to xml

Parse XML in SQL Server

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jan 15, 2015 at 4:49

terary

1,13815 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

How to extract an XML Attribute from an XML varchar string using TSQL in SQL Server 2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related