Need help with this script - 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 2008 Forums

Transact-SQL (2008)

Need help with this script

Author

Topic

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-07 : 12:38:46

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 - 2014-03-07 : 12:42:00

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-07 : 13:02:54

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>'

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-07 : 14:24:45

Thanks Lamprey. It's working so far. Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources