OPENXML Special Characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OPENXML Special Characters

Author

Topic

riaztheman
Starting Member

3 Posts

Posted - 2010-06-30 : 08:11:59

Hi

I'm passing text data in the form of xml to a sql stored proc which uses openxml to insert data into a temp table and then into an actual table.

My problem is that special characters seem to be ignored.

Example - André becomes Andr

The column is of data type varchar.

SQL -

INSERT #accessreport
SELECT *
FROM OPENXML (@hDoc, '/Root/record', 2)
WITH #accessreport

How do i handle this?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-06-30 : 16:59:19

you could collate it on the fly to the proper collation. but what server/database/table collation are you using?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

riaztheman
Starting Member

3 Posts

Posted - 2010-07-01 : 08:03:56

I'm using SQL_Latin1_General_CP1_CI_AS. I'm not sure what you mean by collating it on the fly.

What i dont get is that if i open a query window and run the sp_xml_preparedocument command with the string representation of my xml it parses no problem.

But SQL complains when the exact same code is running. I can see this in the profiler if i run a trace.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-01 : 12:18:12

by on the fly I meant to say in the select statement
SELECT USerFirstName COLLATE [SQL_Latin1_General_CP1_CI_AS] (or whatever collation you wish)
FROM OPENXML (@hDoc, '/Root/record', 2)
WITH #accessreport

would that work for you? also what is length of field name to which you are exporting to. it might just be too short to fit the é :) meaning column might be varchar(4) so riaztheman will be just riaz then you will be just riaz and not da man. and you do not want that do you :)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

riaztheman
Starting Member

3 Posts

Posted - 2010-07-05 : 03:43:53

This is the exact SQL in my stored proc -

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, '<?xml version="1.0" encoding="ISO-8859-1"?><Root><header><datetime>Reader = Multiple Readers from 25/06/2010 00:00:00 to 25/06/2010 23:59:59
DateTime</datetime><lastname>Last Name</lastname><firstname>First
Name</firstname><cardno>CardNo</cardno><state>State</state><reader>Reader</reader></header><record><datetime>25/06/2010
08:30:39</datetime><lastname>Beukes</lastname><firstname>André</firstname><cardno>13904505</cardno><state>Accepted</state><reader>TURNSTILE 4
IN</reader></record></Root>'

CREATE TABLE #accessreport
(
[datetime] VARCHAR(100),
lastname VARCHAR(100),
firstname VARCHAR(100),
cardno VARCHAR(50),
state VARCHAR(50),
reader VARCHAR(50)
)

SELECT *
FROM OPENXML (@hDoc, '/Root/record', 2)
WITH #accessreport

Basically thats what's getting executed. It works fine in a query windows but not through my application.

I get this error - Could not execute Non-Query
The error description is ''A declaration was not closed.''.
Could not find prepared statement with handle 0.
The XML parse error 0xc00ce562 occurred on line number 1, near the XML text "<".
The statement has been terminated

Checked the field lengths and its not that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 04:33:23

I think you should move away from the old XML syntax, and get to learn the new XML syntax in SQL Server 2005 and later.

DECLARE	@Data XML
 
SET @Data = '<?xml version="1.0" encoding="ISO-8859-1"?><Root><header><datetime>Reader = Multiple Readers from 25/06/2010 00:00:00 to 25/06/2010 23:59:59
DateTime</datetime><lastname>Last Name</lastname><firstname>First 
Name</firstname><cardno>CardNo</cardno><state>State</state><reader>Reader</reader></header><record><datetime>25/06/2010 
08:30:39</datetime><lastname>Beukes</lastname><firstname>André</firstname><cardno>13904505</cardno><state>Accepted</state><reader>TURNSTILE 4 
IN</reader></record></Root>' 


SELECT	n.value('datetime[1]', 'VARCHAR(100)') AS [datetime],
	n.value('lastname[1]', 'VARCHAR(100)') AS lastname,
	n.value('firstname[1]', 'VARCHAR(100)') AS firstname,
	n.value('cardno[1]', 'VARCHAR(50)') AS cardno,
	n.value('state[1]', 'VARCHAR(50)') AS [state],
	n.value('reader[1]', 'VARCHAR(50)') AS reader
FROM	@Data.nodes('/Root/record') AS p(n)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources