Troubles with XQuery in SQL server

My daily problems with XML, XQuery in SQL Server 2005.

We save XML in a VARCHAR column.

Why not VARCHAR , if you serialize and de-serialize only in your .net business layer.

But what if you want to create a report on this xml data?
Then you have to extract single elements from this xml string.

That's easy with XQuery in SQL Server, I thought.

That’s our table

Created with this

CREATE TABLE [dbo].[UnfinishedApplication](
 [ApplicationId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [OrganisationId] [uniqueidentifier] NOT NULL,
 [ApplicationName] [varchar](100) NOT NULL,
 [State] [varchar](max) NOT NULL,
 [DateCreated] [datetime] NULL,
 [DateUpdated] [datetime] NULL,
 [EmpUpdated] [varchar](150) NULL,
 [EmpCreated] [varchar](150) NULL,
 [SSWTimestamp] [timestamp] NULL,
 [StepUrl] [varchar](350) NOT NULL,
 CONSTRAINT [PK_UnfinishedApplication] PRIMARY KEY CLUSTERED 
 (
 [ApplicationId] ASC
 )
) 

 

We try this:

select [State].query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]

And get

Msg 4121, Level 16, State 1, Line 2
Cannot find either column "State" or the user-defined function or aggregate "State.query", or the name is ambiguous.

Because we have a VARCHAR column and not an XML column. Let’s convert it to xml, that’s easy :-)

We try this:

select convert(xml,[State]) as tempXml
FROM [UnfinishedApplication]

And get

Msg 9402, Level 16, State 1, Line 2
XML parsing: line 1, character 39, unable to switch the encoding

Arrggghhh, because:

<?xml version="1.0" encoding="utf-16"?> 
   <UnProcessedApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   --- SNIP ----

We serialize this object UnprocessedApplication in our .net business layer :-|
That’s the reason we have this nice character encoding in the xml declaration element :-)

 

Solution?

We just remove the declaration and convert that to XML. Then we can use finally XQuery :-)

SELECT (convert(xml, replace([State],'<?xml version="1.0" encoding="utf-16"?>', ''))).query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]

We created a view for extracting this single fields. But I think a calculated column (COMPUTED COLUMN) would be better for this.
Is this possible todo in a computed column?
Hmm….