CODEWITHSUNDEEP
sql-serverxml
Cylindric
Cylindric

Reputation: 5894

Getting xml data stored in a varchar out of SQL

We have a simple table (an audit log) that our (3rd-party) product fills with data. The problem is that the data appears to be an XML serialisation of data, stored in a varchar(max()) column.

CREATE TABLE Audit (
    AuditID int IDENTITY, 
    AuditData nvarchar(max) NULL
)

The data AuditData then looks a bit like this:

<DELETED customerId="89" 
    username="alice" 
    firstname="Alice" 
    lastname="WTF" 
    field1="..." 
    field2="..." 
    fieldn="..."
/>

Is there any sensible way of getting at this data from SQL queries? For example let us say I want to get all audit records where the customerId is 89.

I don't want to have to resort to string mangling if I can possibly help it!

-- This is bad:
SELECT AuditID, AuditData
FROM Audit 
WHERE AuditData LIKE '%customerId="89"%'

Upvotes: 1

Views: 4185

Answers (2)

gngolakia
gngolakia

Reputation: 2216

Converting in XML will be much easier. Following scripts can help you to get your result.

DECLARE @MyXML XML

SET @MyXML = `<DELETED 
    customerId="89"    
    username="alice" 
    firstname="Alice" 
    lastname="WTF" 
    field1="..." 
    field2="..." 
    fieldn="..."
/>`

SELECT

a.b.value(`'@customerId'`,`'varchar(10)'`) AS customerId

FROM @MyXML.nodes(`'DELETED'`) a(b)

Find More Things

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

It would easier/faster if AuditData was an XML column but you can cast it in the query.

select AuditID, AuditData
from Audit
where cast(AuditData as xml).value('(/DELETED/@customerId)[1]', 'int') = 89

Note: This requires that AuditData column contains valid XML for all rows in the table. (NULL is ok) and SQL Server 2005 or higher.

If you want to filter on customerId regardless of the root node name you can use this instead.

select AuditID, AuditData
from Audit
where cast(AuditData as xml).value('(/*/@customerId)[1]', 'int') = 89

Upvotes: 7

Related Questions