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
INNER JOIN
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: ,,

Shortcuts, SQL Server

View contents of a Stored Procedure (quickly)

I am currently working on a SQL Server 2005 database that has over 500 stored procedures on it. And finding them on the object explorer has been tedious if i just need to view its contents. So i prefer typing in ‘SP_HELPTEXT <procname>’ to view the contents of the procedure. But SQL Server management studio provides an easier way to accomplish this.. You can assign a quick kepboard shortcut to access the contents of the procedure. Just go to Tools -> Options -> Keyboard and type in SP_HELPTEXT for a key of your choice. Now just select the procedure name and hit the shortcut key ..

image

Freeware

Bypass Compulsory Registrations

Ever wanted to read an article and was frustrated to find out that the site that hosts the article requires you to register and login to view the content? If you frequent that website then it makes sense to register. But if its just a one time sneak peek then give this a try: http://www.bugmenot.com

This site gives you free login information for some of the common sites that you might want to take a peek at. Please read their terms of use before using the same.