@sXML is xml string input///
DECLARE @xmlDocumentHandle INT
DECLARE @xmlDocument NVARCHAR(MAX)
SET @xmlDocument = @sXML
EXEC sp_xml_preparedocument @xmlDocumentHandle OUTPUT,@xmlDocument
DECLARE @TempTable TABLE
(
[Row Number] BIGINT,
[EditUserName] NVARCHAR(MAX),
[IsPartner] NVARCHAR(MAX),
[intcdocID] NVARCHAR(MAX),
[intAML] NVARCHAR(MAX),
[intDocType] NVARCHAR(MAX),
[intDocstatus] NVARCHAR(MAX),
[strUrl] NVARCHAR(MAX),
[strcontent] NVARCHAR(MAX),
[psNbr] NVARCHAR(MAX)
)
INSERT @TempTable
SELECT ROW_NUMBER() OVER(ORDER BY [intAML]) AS 'Row Number',
[EditUserName],
[IsPartner],
[intcdocID],
[intAML],
[intDocType],
[intDocstatus],
[strUrl],
[strcontent],
[psNbr]
FROM OPENXML(@xmlDocumentHandle,'/root/compliance/detail',2)
WITH(
[EditUserName] NVARCHAR(MAX) './EditUserName', --#4888
[IsPartner] NVARCHAR(MAX) './IsPartner',
[intcdocID] NVARCHAR(MAX) './intcdocID',
[intAML] NVARCHAR(MAX) './intAML',
[intDocType] NVARCHAR(MAX) './intDocType',
[intDocstatus] NVARCHAR(MAX) './intDocstatus',
[strUrl] NVARCHAR(MAX) './strUrl',
[strcontent] NVARCHAR(MAX) './strcontent',
[psNbr] NVARCHAR(MAX) './psNbr'
)
EXEC sp_xml_removedocument @xmlDocumentHandle