Inserting a constant/variable along with XML in SQL Server 2008

Question

I'm sure this has been answered before but I can't find the right nomenclature to find the answer.

Basically, I have a very simple table with 2 columns, both of data type int

Now, I'm trying to insert n records into the table with one of the columns being provided through an XML as so:

<Data> 
    <DepartmentID>{x}</DepartmentID>
</Data> 

along with the constant value @ProcedureID is being passed along as a variable to the stored procedure.

I'm trying to do the following:

EXEC sp_xml_preparedocument @Handle OUTPUT, @Tags

INSERT INTO Accounting.DepartmentProcedure
(ProcedureID,DepartmentID)
SELECT @ProcedureID,DepartmentID
FROM OPENXML(@Handle,'/Data/DepartmentID',2) WITH (DepartmentID int)

The above parses fine, but running it gives me the following error:

Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'DepartmentID', table 'RCH.Accounting.DepartmentProcedure'; column does not allow nulls. INSERT fails.

For the life of me, I can't figure out how to deal with this issue.

Thanks in advance

Answer 1

I would use the built-in XQuery functionality in SQL Server:

DECLARE @input XML = '<Data> 
    <DepartmentID>{x}</DepartmentID>
</Data>'

INSERT INTO 
    dbo.Accounting.DepartmentProcedure(ProcedureID, DepartmentID)

    SELECT 
        @ProcedureID,
        @input.value('(/Data/DepartmentID)[1]', 'int') 

This inserts the value of <DepartmentID> extracted from the XML into the DepartmentID column.

Go here to read more about the XQuery support in SQL Server 2005 or read the Introduction to XQuery in SQL Server 2005 on MSDN