Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Parse XML stored in Varchar(Max) field

Posted on 2012-03-23
1
760 Views
Last Modified: 2012-05-10
I need to return specific values from XML stored in a varchar(max) field in ms sql server 2008.

MyTable
TransID (PK,int,not null)
Body (varchar(max),null)

select AgreementNumber, FieldName, OldValue, NewValue from (select Body from MyTable where TransID = 1)  

Return

AgreementNumber       FieldName      OldValue          NewValue
123456789               Due Date        8/20/2011        8/26/2011
123456789               Credits Paid      99.71            99.71
.......ad infinitum

Sample XML from Body column

<?xml version="1.0"?>
<EditAgreement>
<AgreeID>1050</AgreeID>
<AgreementNumber>60101050</AgreementNumber>
<CustomerName>Blah blah</CustomerName>
<Edits>
<FieldName>Due Date</FieldName>
<OldValue>8/20/2011</OldValue>
<NewValue>8/26/2011</NewValue>
</Edits>
<Edits>
<FieldName>Credits Paid</FieldName>
<OldValue>99.71</OldValue>
<NewValue>99.71</NewValue>
</Edits>
</EditAgreement>
0
Comment
Question by:griffaw
1 Comment
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37764599
See code sample below.  I've used a variable to imitate your Body varchar(max) column.

First step is to convert it to the xml data type.  Then some xml functions are used to retrieve the data.

Note: you may have to adapt the data types, I've used varchar(100) everywhere.

Additional info:
XML value() method
XML nodes() method
The APPLY operator

declare @xml varchar(max) = 'Your XML Sample';

with XMLData as (
      select convert(xml, @xml) BodyXML
)
select BodyXML.value('(./EditAgreement/AgreeID)[1]', 'varchar(100)') AgreeID
      , edit.value('(./FieldName)[1]', 'varchar(100)') FieldName
      , edit.value('(./OldValue)[1]', 'varchar(100)') OldValue
      , edit.value('(./NewValue)[1]', 'varchar(100)') NewValue
from XMLData
cross apply BodyXML.nodes('/EditAgreement/Edits') t(edit)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
RESTORE A BACKUP IN SQL 2012 from SQL 2008 9 64
Database Integrity 1 49
Transact SQL - Frequency of Length of Distinct Values 3 24
Wordpress Hacked 1 25
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question