CODEWITHSUNDEEP
sql-server-2008sql-server-openxml
Mustafa Ismail Mustafa
Mustafa Ismail Mustafa

Reputation: 237

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

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

Upvotes: 1

Views: 878

Answers (1)

marc_s
marc_s

Reputation: 754668

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

Upvotes: 1

Related Questions