USE tempdb
GO
DROP TABLE XMLResults
GO
CREATE TABLE XMLResults
(Rownumber INT NOT NULL PRIMARY KEY
,ID INT
,Lyric VARCHAR (1000)
)
DROP FUNCTION [dbo].[ufnParseXML]
GO
CREATE FUNCTION [dbo].[ufnParseXML] (@intput varchar(max))
RETURNS @XMLTable TABLE (Rownumber int,ID int, Lyric varchar(150))
AS
BEGIN
DECLARE @XML XML = CONVERT(xml,@intput)
INSERT @XMLTable (Rownumber,ID,Lyric)
SELECT T.c.value('Rownumber[1]','int') AS Rownumber,
CASE WHEN T.c.value('ID[1]','int') = 0 THEN NULL ELSE T.c.value('ID[1]','int')
END AS ID,
CASE WHEN ISNULL(T.c.value('Lyric[1]','varchar(150)'),'') = '' THEN NULL
ELSE T.c.value('Lyric[1]','varchar(150)')
END AS Lyric
FROM @XML.nodes('root/row') AS T(c)
RETURN
END
GO
DROP PROCEDURE dbo.usp_InsertData
go
CREATE PROCEDURE dbo.usp_InsertData (@input VARCHAR(MAX), @ErrorMessage NVARCHAR(1000) OUTPUT)
AS
SET NOCOUNT ON
BEGIN TRY
INSERT INTO XMLResults (Rownumber, ID, Lyric)
SELECT Rownumber, ID, Lyric FROM dbo.ufnParseXML(@input)
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Error Message: ' + ISNULL(ERROR_MESSAGE(),'') +
', Error number: ' + ISNULL(CONVERT(varchar(30),ERROR_NUMBER()),'') +
', Error severity: ' + ISNULL(CONVERT(varchar(30),ERROR_SEVERITY()),'')+
', Error Procedure: ' + ISNULL(ERROR_PROCEDURE(),'') +
', Error Line: ' + ISNULL(CONVERT(varchar(30),ERROR_LINE()),'')
RAISERROR(@ErrorMessage,16,1)
END CATCH
GO
DECLARE @ERR NVARCHAR(1000) = ''
EXEC dbo.usp_InsertData @input='<root>
<row><Rownumber>1</Rownumber><ID>100</ID><Lyric>Not need, just feed the war cann
ibal animal</Lyric></row>
<row><Rownumber>2</Rownumber><ID>200</ID><Lyric>Solo, I"m a soloist on a solo
list, ah live never on a floppy disk</Lyric></row>
<row><Rownumber>3</Rownumber><ID>300</ID><Lyric>Rally round tha family with
a pocket full of shells</Lyric>
</row>
</root>'
,@errormessage=@ERR OUTPUT
SELECT Rownumber, ID, Lyric
FROM XMLResults
Very nice! Tom has implemented this in a production environment with favorable results, and recommends this approach as a substitute for TVPs when using an ORM tool such as EF.