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….
4 comments:
Or you can also use something like:
SELECT (convert(xml, convert(nvarchar(max), [State]))).query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]
Hi Anonymous
Yes that works!
Thanks heaps!
I didn't figure out that I had to convert it to nvarchar!!
Unfortunately for me, the data I'm working with is a mix of UTF-8 & UTF-16, which seems to break Anonymous's solution.
However, Peter, with a minor modification, your solution works. The modification:
convert(xml, replace(replace(convert(nvarchar(max), xmlfieldname), '',''), '',''),2)
This has had a side-benefit of getting rid of an error with one record claiming to contain an invalid character.
Works great for me. In my case, I had to cast the field to varchar(max), because it's a text field
Post a Comment