MSSQL store procedure to convert from XML to table

Pick Language to Auto Translate:
AR | BG | CA | CS | DA | DE | EL | ES | FI | FR | HI | HR | ID | IT | IW | JA | KO | LT | LV | NL | NO | PL | PT | RO | RU | SK | SR | SL | SV | TL | UK | VI | ZH | ZH-TW

Submitted by chanh on

Below is the actual code so you can just copy and paste to create the store procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spXML2Table]
(
-- output from for xml raw
@XmlDocument XML = '',
@Schema varchar(max) = 'ID INT, InterfaceID int, FldPos varchar(20), FldLen varchar(20), FldName varchar(20), RecType varchar(20)'
)
AS
BEGIN
declare @var_query nvarchar(max)
--create table #T (ID INT Identity(1,1),'+@Schema+')
SET @var_query = '
create table #T ('+@Schema+')
DECLARE @DocHandle int;
DECLARE @XmlDocument XML;
SET @XmlDocument = ''' +CAST(@XmlDocument AS NVARCHAR(max)) + ''';
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
insert into #T
SELECT *
FROM OPENXML (@DocHandle, ''/doc/row'',1)
WITH ( '+ @Schema +' );
select * from #T

exec dbo.sp_executesql @statement = @var_query

return
END

Forums: