even though u successfully loaded the raw xml into a table you still processed the data one row at a time. It is much better to use OPENXML and pivot the data.

example:

--TABLE TO HOLD THE RAW XML

DECLARE @tbl TABLE

(

ID INT, ParentID INT, NodeType INT, LocalName NVARCHAR(100), Prefix VARCHAR(50),

NameSpaceURI VARCHAR(50), DataType VARCHAR(50), Prev VARCHAR(50), [TEXT] NVARCHAR(200)

)

--xml in a variable of XML type that might be used in a proc or fnc

EXEC sp_xml_preparedocument @docHandle OUTPUT,@ItemsXML;

INSERT INTO @tbl(

ID ,ParentID ,NodeType ,LocalName,Prefix ,NameSpaceURI ,DataType ,Prev ,[TEXT]

)

SELECT * FROM OPENXML(@docHandle, '/PackageRequests/ItemRequest',1)

EXEC sp_xml_removedocument @docHandle

;

WITH CTE_Ranked AS (

SELECT

T1.LocalName

,T2.Text

,NTILE(12) OVER (PARTITION BY T1.LocalName ORDER BY T1.ID) AS RNKING

--NTILE Count defined by counting the largest number of possible records for any one column

--this could easily be a variable

FROM @tbl T1

INNER JOIN @Tbl T2

ON T1.ID = T2.ParentID

WHERE T1.LocalName IN ('VndID','LocID')

),

CTE_PIVOT_SOURCE AS (

SELECT

CTE_Ranked0.LocalName AS RLN

,CTE_Ranked0.text AS RIV

,CTE_Ranked01.RNKING

FROM CTE_Ranked CTE_Ranked0

INNER JOIN CTE_Ranked CTE_Ranked01 ON CTE_Ranked01.RNKING = CTE_Ranked0.RNKING

)

SELECT VendorID,LocationID

FROM CTE_PIVOT_SOURCE

PIVOT (

MIN(RIV)

FOR RLN IN (VndID,LocID)

) AS PivotTable