Daily Archives: October 26, 2011

SQL Server: Create and drop a temporary table

The following shows a basic example of how to create a temporary table, insert some data and select the data out:
CREATE TABLE #TEMPTBL
(IsSomething BIT,
IntValue BIGINT)

INSERT INTO #TEMPTBL (IsSomething, IntValue) VALUES (1, 1234)

SELECT * FROM #TEMPTBL

When scripting with temporary tables it’s more difficult to figure out if the table exists or not (i.e. you can’t use the normal mechanism), so I simply wrap the drop table command in a try catch block and ignore any errors:

BEGIN TRY
DROP TABLE #TEMPTBL
END TRY
BEGIN CATCH
END CATCH

XPath to return non-empty values

This is an example of an XPath I’ve used before to get the nodes with a non-empty attribute:
// Where rows have a non-empty subtag called field with a name attribute equal to attrib1, select the rows
XmlNodeList nodeList = xmlDocument.SelectNodes("./idc:row[./idc:field[@name='attrib1'][text()]]", xmlnsmgr);

Note: the key part in this XPath is the text() part.

I’m not totally convinced this is the correct XPath to be using.
Of course there is also the node() function.
Combining these with not (e.g. not(node())) may yield the correct way to do it.

For further information try:
W3C XML Path Language (XPath) Spec
MSDN XmlNode.SelectNodes Method