XML parameter in SPs - SQL Code

declare @DOC XML = '<ROOT>
<PARAMS>
<ASSIGNMENT
Sales_Employee_ID="00492027"
QC_Account_ID="1301"
AccountID="132"
OwnerEmployeeID="20271036"
CountryHierarchyLevelID="7"
CountryHierarchyLevelValue="US"
BGID="1" />
</PARAMS>
</ROOT>'


DECLARE @TEMPACCOUNTASSIGNMENT TABLE
(
Sales_Employee_ID CHAR(8),
QC_Account_ID INT,
AccountID INT,
OwnerEmployeeID CHAR(8),
GeographyAreaID INT,
Country_Hierarchy_Level_ID INT,
Country_Hierarchy_Level_Value VARCHAR(10),
BG_ID INT
)

DECLARE @iDoc INT;

EXECUTE SP_XML_PREPAREDOCUMENT @iDoc OUTPUT, @Doc

INSERT INTO @TEMPACCOUNTASSIGNMENT (Sales_Employee_ID,QC_Account_ID,AccountID,OwnerEmployeeID,GeographyAreaID,Country_Hierarchy_Level_ID,Country_Hierarchy_Level_Value,BG_ID)
SELECT Sales_Employee_ID, QC_Account_ID , AccountID,OwnerEmployeeID,GeographyAreaID,CountryHierarchyLevelID,CountryHierarchyLevelValue,BGID
FROM OPENXML (@iDoc, '/ROOT/PARAMS/ASSIGNMENT', 1)
WITH (Sales_Employee_ID CHAR(8), QC_Account_ID INT, AccountID INT,OwnerEmployeeID CHAR(8),GeographyAreaID INT, CountryHierarchyLevelID INT,
CountryHierarchyLevelValue VARCHAR(10), BGID INT)

EXECUTE SP_XML_REMOVEDOCUMENT @iDoc

No comments: