problem with xml as input to stored procedure

I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @.XMLString XML
Set @.XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @.XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

In my sp i m using it as : .

Insert Into Company(

CompanyName,

Code,

)

Output Inserted.CompanyId Into @.tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@.CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@.Code' , 'varchar(6)'),

FROM @.XMLString.nodes('Company') AS ParamValues(Item)

Its not only this but there are other special characters which create problem like '@.' and many more...

How to resolve it?
plzz do help at the earliest...

The ampersand is a "special" character.

Here is an article about this and how to handle it: http://www.xml.com/pub/a/2001/01/31/qanda.html

|||

Thanx a lot Louis....

u saved me from a big problem and that too on time....

Thanx once again..today i understood the power of microsoft forums...its been really helpful to me..

|||

i dont know how to close a thread or mark it as answered..i dont find any tab which says that my post has been answered...

|||I done it for you.

|||Thanx

problem with xml as input to stored procedure

I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @.XMLString XML
Set @.XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @.XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

In my sp i m using it as : .

Insert Into Company(

CompanyName,

Code,

)

Output Inserted.CompanyId Into @.tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@.CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@.Code' , 'varchar(6)'),

FROM @.XMLString.nodes('Company') AS ParamValues(Item)

Its not only this but there are other special characters which create problem like '@.' and many more...

How to resolve it?
plzz do help at the earliest...

The ampersand is a "special" character.

Here is an article about this and how to handle it: http://www.xml.com/pub/a/2001/01/31/qanda.html

|||

Thanx a lot Louis....

u saved me from a big problem and that too on time....

Thanx once again..today i understood the power of microsoft forums...its been really helpful to me..

|||

i dont know how to close a thread or mark it as answered..i dont find any tab which says that my post has been answered...

|||I done it for you.

|||Thanx

problem with wildcard character search

Hi,
I have this problem in a stored procedure.
I have a field called "PartDescription" in a database table which accepts
both "_" and "%" characters, But when I write a query for searching based on
"PartDescription"
field and I enter "_" in the search field then it treats it as a wildcard
character and performs the search.
I am sending the search parameter to the sql server stored procedure. Is
there any other efficient way of escaping these wildcard characters?
Currently I am using code like this
CREATE PROCEDURE ihmtprocSearchLabCabRAIL_sp
AS
DECLARE @.v_vr_escape_char VARCHAR(1)
SET @.v_vr_escape_char = CHAR(220)
SELECT PartDescription
FROM
PartData
WHERE
PartDescription LIKE
REPLACE(REPLACE(@.proc_vr_part_desc,'_',@.
v_vr_escape_char + '_'),'%',
@.v_vr_escape_char + '%') + '%' ESCAPE @.v_vr_escape_char OR
@.proc_vr_part_desc IS NULL)Hi
Posting DDL and Example data always helps
http://www.aspfaq.com/etiquett_e.asp?id=5006
This seems fine:
CREATE TABLE PartData ( id int not null identity(1,1), PartDescription
varchar(400) )
INSERT INTO PartData ( PartDescription )
SELECT 'This is part_1'
UNION ALL SELECT 'This is part%2'
UNION ALL SELECT 'This is part 3'
SELECT * FROM PartData
CREATE PROCEDURE ihmtprocSearchLabCabRAIL_sp @.proc_vr_part_desc varchar(400)
AS
DECLARE @.v_vr_escape_char CHAR(1)
SET @.v_vr_escape_char = CHAR(220)
SELECT PartDescription
FROM PartData
WHERE @.proc_vr_part_desc IS NULL
OR PartDescription LIKE '%' +
REPLACE(REPLACE(@.proc_vr_part_desc,'_',@.
v_vr_escape_char + '_'),'%',
@.v_vr_escape_char + '%') + '%' ESCAPE @.v_vr_escape_char
EXEC ihmtprocSearchLabCabRAIL_sp @.proc_vr_part_desc = 'part'
EXEC ihmtprocSearchLabCabRAIL_sp '1'
EXEC ihmtprocSearchLabCabRAIL_sp '%'
EXEC ihmtprocSearchLabCabRAIL_sp '_'
EXEC ihmtprocSearchLabCabRAIL_sp '_1'
The only change is to add a leading '%' and varchar(1) does not make much
sense!
John
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:%23%23%23NY5oZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have this problem in a stored procedure.
> I have a field called "PartDescription" in a database table which accepts
> both "_" and "%" characters, But when I write a query for searching based
> on
> "PartDescription"
> field and I enter "_" in the search field then it treats it as a wildcard
> character and performs the search.
> I am sending the search parameter to the sql server stored procedure. Is
> there any other efficient way of escaping these wildcard characters?
> Currently I am using code like this
> CREATE PROCEDURE ihmtprocSearchLabCabRAIL_sp
> AS
> DECLARE @.v_vr_escape_char VARCHAR(1)
> SET @.v_vr_escape_char = CHAR(220)
> SELECT PartDescription
> FROM
> PartData
> WHERE
> PartDescription LIKE
> REPLACE(REPLACE(@.proc_vr_part_desc,'_',@.
v_vr_escape_char + '_'),'%',
> @.v_vr_escape_char + '%') + '%' ESCAPE @.v_vr_escape_char OR
> @.proc_vr_part_desc IS NULL)
>

Problem with WHERE clause when column length > 255

Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work...any ideas? Is it a driver bug? Anyone know of a driver that will work?

Sample code...

ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}

Thanx!Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?

-PatP|||Originally posted by Pat Phelan
Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?

-PatP

Yes, I have and it returns results. Additionally, I've removed the column from the where clause in my java file and it returns data, but of course its uselys because I need that column in the where clause...

Thanks...|||Hmm... Microsoft's JDBC driver says it will support upt to 4000 character strings. Have you used profiler to see exactly what's being sent to your SQL server?|||Since the SQL code works ok from Query Analyzer, we can assume that SQL Server to the MDAC on your client works fine. The problem must be in the JDBC driver or beyond it.

Which JDBC driver are you using? Does it have any length limitations that you are aware of (or does it keep them as surprises for the unwary)? As Peter pointed out, the Microsoft JDBC driver handles big strings (I've run 2 Kb columns out through it).

Just as another thought, are you certain that your Java app is behaving and actually submitting what you expect it to.

-PatP