SQL Server

Join Multiple OPENXML queries

If you ever wanted to run multiple openxml queries and join them give this a go:

DECLARE @idoc int
DECLARE @idoc1 int
DECLARE @doc varchar(1000)
DECLARE @doc1 varchar(1000)
SET @doc ='<ROOT><Customer CustomerID=”1″ ContactName=”Balaji Ramesh” /></ROOT>’
SET @doc1 ='<ROOT><Customer CustomerID=”1″ ContactNumber=”1234567890″ /></ROOT>’
–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
EXEC sp_xml_preparedocument @idoc1 OUTPUT, @doc1
— Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT d1.*, d2.ContactNumber
FROM  OPENXML (@idoc, ‘/ROOT/Customer’,1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20)) as d1
OPENXML (@idoc1, ‘/ROOT/Customer’,1)
            WITH (CustomerID  varchar(10),
                  ContactNumber varchar(20)) as d2
ON d1.CustomerID = d2.CustomerID

EXEC sp_xml_removedocument @idoc
EXEC sp_xml_removedocument @idoc1

This is a simple example but you get the idea.

Technorati Tags: ,,


One thought on “Join Multiple OPENXML queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s