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

clip_image002

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….

LINQ: Remember to use the let keyword

 

Sample code from http://www.codethinked.com/post/2008/04/The-Linq-quot3bletquot3b-keyword.aspx

How to filter for names that are 4 or 5 and startwith or endwith vowel?
My description is more difficult to read than the actual LINQ query.

Check it out!
namelist = List<string> { …with some names… };

var names = (from p in nameList 
                let vowels = new List<string> { "A", "E", "I", "O", "U" } 
                let startsWithVowel = vowels.Any(v => p.ToUpper().StartsWith(v)) 
                let endsWithVowel = vowels.Any(v => p.ToUpper().EndsWith(v)) 
                let fourCharactersLong = p.Length == 4 
                let fiveCharactersLong = p.Length == 5 
            where 
                (startsWithVowel || endsWithVowel) && 
                (fourCharactersLong || fiveCharactersLong) 
                select p).ToList();

Latest Posts

Popular Posts