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

marc_s · Accepted Answer · 2012-02-26 11:30:27Z

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

edited Feb 26, 2012 at 11:30

answered Feb 26, 2012 at 11:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mustafa Ismail Mustafa Over a year ago

Kudos man, can you suggest any references for the built in XQuery functionality?

marc_s Over a year ago

@MustafaIsmailMustafa: I have two links in my answer- those are good starting points. Otherwise: search for "XQuery" on MSDN - tons of material there

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related