SQL OPENXML - Cool Stuff

I came across this really useful code earlier today and I had to share. This is an example of using XML and SQL to update multiple things at once.

First inside of your code you need to generate the XML. Here's an example of an already-generated XML doc:

<root>
<item someID="2245" crewName="G" />
<item someID="2245" crewName="V" />
<item someID="2245" crewName="O" />
<item someID="2245" crewName="Q" />
<item someID="2245" crewName="M" />
<item someID="2245" crewName="B" />
</root>

CREATE PROCEDURE dbo.exampleOXML (
@someID int,
@XMLItemList text
) AS

DECLARE @tCount INT
SET @tCount = @@TRANCOUNT

IF @tCount = 0
BEGIN TRAN exampleOXML
ELSE
SAVE TRAN exampleOXML

-- BEGIN OUR COOL STUFF
DECLARE @myXMLDoc int

-- Add our document
EXEC sp_xml_preparedocument @myXMLDoc OUTPUT, @XMLItemList

-- Use OPENXML
SELECT
someID, crewName
FROM
OPENXML (@myXMLDoc, '/root/item', 1) WITH (someID INT, crewName VARCHAR(4))

-- Remove our document
EXEC sp_xml_removedocument @myXMLDoc

IF @@ERROR <> 0
ROLLBACK TRAN exampleOXML
ELSE IF @trancount = 0
COMMIT TRAN exampleOXML

GO

You can also use the following, assuming all parameters match:

OPENXML (@myXMLDoc, '/root/item', 1) WITH item