XML Question - 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)

XML Question

Author

Topic

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-25 : 16:02:21

Hi,
I have this SQL script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test]
@String VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
--Delete work table first
TRUNCATE TABLE Test
BEGIN TRY
DECLARE @str VARCHAR(max)
SET @str = @String

DECLARE @strXML XML
SET @strXML = '<table><row><col><![CDATA[' + REPLACE(REPLACE(@str,'~',']]></col></row> <row><col><![CDATA['),'|',']]></col><col><![CDATA[') + ']]></col></row></table>'

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

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
FROM @XML.nodes('/table/row') AS line(col)
END TRY

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

Usage:
EXEC Test '1|Test1~2|Test2'

It worked fine but can you help me modify the code for @strXML so it will work with below string:

EXEC Test '"1"|"Test1"~"2"|"Test2"'
Note: The string using text qualifier of ""

Thanks,
Tony

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-25 : 16:24:56

use this instead of SET @strXML in your code and it will work..

SET @strXML = '<table><row><col><![CDATA[' + REPLACE(REPLACE(REPLACE(@str,'~',']]></col></row> <row><col><![CDATA['),'|',']]></col><col><![CDATA['), '"', '') + ']]></col></row></table>'

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

tonydang2002
Starting Member

15 Posts

Posted - 2014-03-25 : 16:39:57

Thank you very much. That worked!!!...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources