XML Pass and insert into Table

DECLARE @ProductMapping XML

SET @ProductMapping='

Table1>
ProductId>1
InclusionDescription>0
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
Table1>
ProductId>2
InclusionDescription>0
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
Table1>
ProductId>3
InclusionDescription>0
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>0
SubCatID>1
CreditLimit>10
Quantity>0
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>0
SubCatID>2
CreditLimit>10
Quantity>0
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>xxx
SubCatID>0
CreditLimit>0
Quantity>123
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>yyy
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
/DocumentElement>'


SELECT
PackageInclus.COL.value('ProductId[1]','INT') AS ProductId,
PackageInclus.COL.value('InclusionDescription[1]','NVARCHAR(500)') AS InclusionDescription,
PackageInclus.COL.value('SubCatID[1]','INT') AS SubCatID,
PackageInclus.COL.value('CreditLimit[1]','MONEY') AS CreditLimit,
PackageInclus.COL.value('Quantity[1]','INT') AS Quantity,
PackageInclus.COL.value('CreatedBy[1]','INT') AS CreatedBy
FROM @ProductMapping.nodes(N'//DocumentElement/Table1') AS PackageInclus(COL);

UnPivot Table Like (Colum to Row) Stored Procedure

ALTER PROCEDURE UNPIVOTTABLE
(
@TABLENAME AS VARCHAR(MAX),
@WHERECLAUSE AS VARCHAR(MAX) = NULL

)
AS
BEGIN


DECLARE @COLUMN_NAME_ORIGNAL AS VARCHAR(MAX)
DECLARE @COLUMN_NAME AS VARCHAR(MAX)
DECLARE @SQL AS VARCHAR(MAX)

SET @COLUMN_NAME = ''
SET @SQL = ''
SET @COLUMN_NAME_ORIGNAL = ''



SELECT
@COLUMN_NAME_ORIGNAL = @COLUMN_NAME_ORIGNAL + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME



SELECT
@COLUMN_NAME = @COLUMN_NAME + 'ISNULL(' + 'CAST(' + CAST(ISNULL(COLUMN_NAME,'') AS NVARCHAR(MAX)) + ' AS VARCHAR(MAX)) ,'+ CHAR(39) + '' + CHAR(39) + ') '+ COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME

SELECT @COLUMN_NAME = SUBSTRING(@COLUMN_NAME,0,LEN(@COLUMN_NAME))
SELECT @COLUMN_NAME_ORIGNAL = SUBSTRING(@COLUMN_NAME_ORIGNAL,0,LEN(@COLUMN_NAME_ORIGNAL))


SET @SQL = 'SELECT ' + @COLUMN_NAME + ' INTO ##TEMPUNPIVOT FROM ' + @TABLENAME + + CASE WHEN @WHERECLAUSE IS NULL THEN '' ELSE ' WHERE ' + @WHERECLAUSE END

EXEC (@SQL)


--SELECT @COLUMN_NAME_ORIGNAL

--SELECT * from ##TEMPUNPIVOT

SET @SQL = 'SELECT COLUMN_NAME,COLUMN_VALUE FROM (
SELECT ' + @COLUMN_NAME_ORIGNAL
+ ' From ##TEMPUNPIVOT
) OBJ
UNPIVOT (
COLUMN_NAME FOR
COLUMN_VALUE IN ('
+ @COLUMN_NAME_ORIGNAL + '
)
) AS UNpvt'



--PRINT @SQL

EXEC (@SQL)

DROP TABLE ##TEMPUNPIVOT
END