SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with this script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tonydang2002
Starting Member

15 Posts

Posted - 03/07/2014 :  12:38:46  Show Profile  Reply with Quote
Hi, I have a SP below that works and give me the data I want. However, when there is special characters like "&" in data, it errors out. Can someone help me how to work around this. Alter the SP that it can take all special characters without errors.

Thank you very much!
Tony

Test case:
This works great
EXEC [Datasplit] '1|row1~2|row2~3|row3'

desired Result:
TestID TestDesc
1 row1
2 row2
3 row3

This doesn't work due to a "&" in data
EXEC [Datasplit] '1|row1~2|row2&~3|row3'

Error:
fail to load data due to error: XML parsing: line 1, character 74, illegal name character

My SP:
CREATE PROCEDURE [dbo].[datasplit]
@String VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
--Delete work table first
TRUNCATE TABLE TEST

--Load work table from SAP(This SP is called from SAP to feed data into wrk tale from SAP)
BEGIN TRY

DECLARE @str VARCHAR(max)
SET @str = @String

DECLARE @strXML VARCHAR(max)
SET @strXML = '<table><row><col>' + REPLACE(REPLACE(@str,'~','</col></row> <row><col>'),'|','</col><col>') + '</col></row></table>'

DECLARE @XML XML
SET @XML = CAST(@strXML AS XML)

INSERT TEST
SELECT
line.col.value('col[1]', 'varchar(1000)') AS col1
,line.col.value('col[2]', 'varchar(1000)') AS col2
,line.col.value('col[3]', 'varchar(1000)') AS col3
,line.col.value('col[4]', 'varchar(1000)') AS col4
,line.col.value('col[5]', 'varchar(1000)') AS col5
FROM @XML.nodes('/table/row') AS line(col)
END TRY

BEGIN CATCH
PRINT 'CHAIN_ACCT_MBRSHP_wrk_T did not load due to error: ' + ERROR_MESSAGE();
END CATCH
END

tonydang2002
Starting Member

15 Posts

Posted - 03/07/2014 :  12:42:00  Show Profile  Reply with Quote
sorry here is my correct SP:
CREATE PROCEDURE [dbo].[Datasplit]
@String VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
--Delete work table first
TRUNCATE TABLE TEST

--Load work table from SAP(This SP is called from SAP to feed data into wrk tale from SAP)
BEGIN TRY

DECLARE @str VARCHAR(max)
SET @str = @String

DECLARE @strXML VARCHAR(max)
SET @strXML = '<table><row><col>' + REPLACE(REPLACE(@str,'~','</col></row> <row><col>'),'|','</col><col>') + '</col></row></table>'

DECLARE @XML XML
SET @XML = CAST(@strXML AS XML)

INSERT TEST
SELECT
line.col.value('col[1]', 'varchar(1000)') AS col1
,line.col.value('col[2]', 'varchar(1000)') AS col2
FROM @XML.nodes('/table/row') AS line(col)
END TRY

BEGIN CATCH
PRINT 'fail to load data due to error: ' + ERROR_MESSAGE();
END CATCH
END
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/07/2014 :  13:02:54  Show Profile  Reply with Quote
I don't do a lot with XML, but I think if you wrap the text with a CDATA section that'll solve your issue:
SET @strXML = '<table><row><col><![CDATA[' + REPLACE(REPLACE(@str,'~',']]></col></row> <row><col><![CDATA['),'|',']]></col><col><![CDATA[') + ']]></col></row></table>'

Go to Top of Page

tonydang2002
Starting Member

15 Posts

Posted - 03/07/2014 :  14:24:45  Show Profile  Reply with Quote
Thanks Lamprey. It's working so far. Thank you for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000