Problem with CDATA - 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)

Problem with CDATA

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2011-09-08 : 07:20:28

I've the following code in the stored procedure

DECLARE @RowInfoListConsumption = '<Root><Rows><Row><ProductName><![CDATA[EB - Royalty 'N Fee]]></ProductName></Row></Rows></Root>'

Create Table #Temp2
(
ProductName VARCHAR(MAX),
)

DECLARE @DocHandle INT

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @RowInfoListConsumption

INSERT INTO #Temp2(ProductName)
Select ProductName
FROM OPENXML (@DocHandle,'/Root/Rows/Row', 2) WITH
(
ProductName VARCHAR(50)
)

When I try running the above procedure, it says incorrect syntax near N.

The productname has a quote in it. However it has enclosed within the CDATA section. Is there a way that the SQL server reads it without any issues.

Awaiting your reply

Thanks in advance

Sachin.Nand

2937 Posts

Posted - 2011-09-08 : 07:27:25

[code]
DECLARE @RowInfoListConsumption xml = '<Root><Rows><Row><ProductName><![CDATA[EB - Royalty ''N Fee]]></ProductName></Row></Rows></Root>'

Create Table #Temp2
(
ProductName VARCHAR(MAX),
)

DECLARE @DocHandle INT

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @RowInfoListConsumption

INSERT INTO #Temp2(ProductName)
Select ProductName
FROM OPENXML (@DocHandle,'/Root/Rows/Row', 2) WITH
(
ProductName VARCHAR(50)
)

select * from #Temp2

Drop table #Temp2

[/code]

PBUH

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 07:37:53

need to double up the single quotes, and add a data type to the @RowInfoListConsumption variable.

DECLARE @RowInfoListConsumption xml = '<Root><Rows><Row><ProductName><![CDATA[EB - Royalty ''N Fee]]></ProductName></Row></Rows></Root>'

edit: oops, PHUH beat me to it while I was getting coffee

sridhar3004
Starting Member

34 Posts

Posted - 2011-09-08 : 07:39:30

Thank you Sachin
The product name can contain any special character like single quote, double quote etc.

Doesn't CDATA parse these special characters by itself?
Do I need to have an escape character within the CDATA element?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 07:44:55

You do. SQL Server doesn't see the CDATA section as anything special. Just part of the string.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources