XQeury with parameter in a DataSet

Today, I had to write a query that would retrieve a customerId from an xml data field in a SQL Server datatable. First of all, I noticed that the XQuery is case sensitive. After figuring this out, I started writing the query in SQL Server Management Studio and it started somewhat like this:

DECLARE @customerId NVARCHAR(1024)
SET @customerId = 'some subscriber id'

SELECT Id, Metadata
FROM Customers
WHERE Metadata.exist('/customerMetadata/subscriber[@Id=”@customerId"]') = 1

But wait… How do I make the distinction between an xml attribute and a variable? After googling for a while, I learned that I had to use the “sql:variable(‘@customerId’) construct for this. So I changed the query to:

DECLARE @customerId NVARCHAR(1024)
SET @customerId = ' some subscriber id '

SELECT Id, Metadata
FROM Customers
WHERE Metadata.exist('/customerMetadata/subscriber[@Id=sql:variable("@customerId")]') = 1

Happy as I was, I copied it into the “TableAdapter Query Configuration” Wizard of Visual Studio 2005. But I got disappointed again. The query wizard ended with the following messages (see picture below):

XQuery: Unable to resolve sql:variable(‘@customerId’). The variable must be declared as a scalar TSQL variable.

What I tried next was to manually add the variable to the variable collection of that query (see below).

And finally it worked! With some hope on better guidance, I tried the same query in Visual Studio 2008. Unfortunately, this had the same result.

Xslt transformation to ASCII file and unicode characters

Today, I had to create a csv text file in plain ASCII text. The source data was in xml format.

The first solution that comes in mind is the use of an xslt transformation to tranform the xml file into the ASCII text file. However, the xml file can (and will) contain unicode characters. According to the spec, these characters must be replaced by a '^' character in the csv file.

This can be accomplished by specifying an Encoding type with an EncoderReplacementFallback instance. See the code below:

//Create an XsltArgumentList.
XsltArgumentList args = new XsltArgumentList();
...

// Create the transformation class
XslCompiledTransform xslt = new XslCompiledTransform(true);

// Load the xslt file
xslt.Load(config.TransformationFile);

// Create an encoding type so that unicode characters are translated into a '^'
Encoding encoding = Encoding.GetEncoding("us-ascii",
new EncoderReplacementFallback("^"),
new DecoderReplacementFallback("ERROR"));

// Create an XmlTextWriter to output to file.
using (TextWriter writer = new StreamWriter(csvOutputFilename, false, encoding))
xslt.Transform(reader, args, writer);

What happens here is that a 'us-ascii' Encoding type is created with an EncoderReplacementFallback instance. This ensures that all characters that are not part of the 'us-ascii' encoding are replaced by the '^' character.

This encoding is later used for creating the TextWriter with the specified encoding.

For more information see the microsoft documentation:
http://msdn2.microsoft.com/en-us/library/system.text.encoderreplacementfallback.aspx

Hi y'all

Hi everyone. It's me, Raymond Herwarts from Helmond, The Netherlands.

I'm new to blogs and I don't know if I have the patience and time to fill it on a regular basis. But I will give it a try.