CREATE PROCEDURE [ImportJobMaterials]
    @JobMaterialsXml AS VARCHAR(MAX),
    @JobID AS INT,
    @ChangeOrderNumber AS VARCHAR(10) = NULL,
    @ChangeOrderDescription AS VARCHAR(100) = NULL
AS
    BEGIN
        DECLARE @XML AS XML
        
        DECLARE @MaterialsTable TABLE
        (
            ID INT IDENTITY(1,1),
            Area VARCHAR(250),
            Phase VARCHAR(250),
            WorkCodeID INT,
            WorkCodeTitle VARCHAR(250),
            MaterialTitle VARCHAR(250),
            Quantity DECIMAL(18,2),
            TotalHours DECIMAL(18,2)
        )
        
        SELECT @XML = @JobMaterialsXml
        
        INSERT INTO @MaterialsTable (Area, Phase, WorkCodeID, WorkCodeTitle, MaterialTitle, Quantity, TotalHours)
        SELECT M.Item.query('./Area').value('.','VARCHAR(250)') Area,
               M.Item.query('./Phase').value('.','VARCHAR(250)') WorkCode,
               M.Item.query('./WorkCodeID').value('.','INT') WorkCodeID,
               M.Item.query('./WorkCodeTitle').value('.','VARCHAR(250)') WorkCodeTitle,
               M.Item.query('./MaterialTitle').value('.','VARCHAR(250)') MaterialTitle,    
               M.Item.query('./Quantity').value('.','DECIMAL(18,2)') Quantity,    
               M.Item.query('./TotalHours').value('.','DECIMAL(18,2)') TotalHours
        FROM @XML.nodes('/ArrayOfJobMaterialImport/JobMaterialImport') AS M(Item)
        
        --Process the data                    
    END
 
 
No comments:
Post a Comment