How to change encoding for XML Column

I am updating an xml column in SQLServer 2005, but it always gives me
an error
"XML parsing: line 1, character 38, unable to switch the encoding"
my csharp is like the following
cmd.Parameters.Add("@.xml", SqlDbType.Xml);
cmd.Parameters["@.xml"].Value =xmldata;
My xml have the following line
"<?xml version="1.0" encoding="UTF-8"?>..."
If I remove this line, everything is OK, looks like the SQLParameter is
set to UTF-16 and my xml data is set to UTF-8, how do I make
SQLParameter take UTF-8? I did not see any properties under
SQLParameter to change the encode.
Please advice.
Thanks in advance.
JohnWell I do search a bit and perhaps you want to remove that line before you
insert it in to the column.
chanmm
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>|||Try using SqlDbType.VarChar (even if your parameter in the proc is really
XML). This worked for me. Reason is likely that you're using a System.String
(Unicode) and the XML data type is internally UTF-16, so telling it UTF-8 is
switching encodings, after its "decided" you wanted UTF-16. There is
automatic string conversion if the encoding matches the SQL varchar data
type.
Bear in mind, you'll always get the data out as UTF-16.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>

how to capture o/p of SELECT .. FOR XML AUTO.

Hi,
Can u please tell me how can i store o/p of following query into a variable
and process it.
"SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
Thanks
Gopinath M.
"Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can u please tell me how can i store o/p of following query into a
variable
> and process it.
> "SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
You can't store the results of a for xml query just within SQL. The XML is
created by the provider.
Bryant
|||There are some ugly workarounds using the sp_OA stored procedures.
In SQL Server 2005 (beta2 comming soon, watch this space for nomination
registration), you will be able to do so.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:Ogsvi70EEHA.3568@.tk2msftngp13.phx.gbl...
> "Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
> news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> variable
> You can't store the results of a for xml query just within SQL. The XML is
> created by the provider.
> --
> Bryant
>

How to build dynamic Xquery

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.