Extended Stored Procedures: Intro And 10 Cool Examples - Examples (contd.)

(Page 4 of 6 )

Proc #4: xp_dirtree

Sometimes it's handy to have access to the directory structure of the file system through SQL Server. The xp_dirtree is a handy extended store procedure that traverses a directory and returns the details of all of its sub-directories as records. Its signature looks like this:

xp_dirtree parent_directory

To test this extended stored procedure, jump to the DOS prompt (Yes, I know it's old school, but I'm an old school kinda guy) and create some directories like this:

C:\>md mydir

C:\>cd mydir

C:\mydir>md subdir1

C:\mydir>md subdir2

C:\mydir>cd subdir1

C:\mydir\subdir1>md subsubdir1

Enter the following line into Query Analyzer:

exec master..xp_dirtree 'c:\mydir'

After running the query, the results in the output window look like this:



As you can see from the results set above, each sub-directories name is returned along with a depth value, which is relative to the directory passed to the extended stored procedure. For example, if I passed in c:\blah and that directory contained another directory named c:\blah\blah1, then blah1's depth would be one, because it's one directory level deep.

Proc #5: sp_xml_preparedocument

The sp_xml_preparedocument extended stored procedure takes an XML document, parses it using the MSXML2 parser, and provides the parsed document, which is now ready for consumption using SQL Servers various other XML-related procedures. The signature of the sp_xml_preparedocument extended stored procedure looks like this:

sp_xml_preparedocument hdoc OUTPUT [, xmltext] [, xpath_namespaces]

The first parameter, hdoc, is an integer handle that is used to identify the XML document to SQL Server. The second parameter, xmltext, is the actual XML document that SQL Server should tell the MSXML2 library to parse, and the third option is a set of OPENXML XPath namespaces that can be used to format the resultant XML document.

To test the sp_xml_preparedocument extended stored procedure, enter the following code into Query Analyzer:

declare @id int

declare @xml varchar(500)

set @xml = '<people><person name="John Doe" age="30"/></people>'

exec sp_xml_preparedocument @id output, @xml

select * from OpenXML(@id, '/people/person')

with(name varchar(50) '@name')

exec sp_xml_removedocument @id

The code above creates a simple XML string containing a root element and one child element. We use the sp_xml_preparedocument extended stored procedure to load and parse the XML string, returning the ID of the internal representation of the XML document to @id.

To actually retrieve data from the XML, we use a normal select statement in combination with the OpenXML function, which takes the ID of the XML to load, as well as an XPath pattern that specifies the hierarchy to extract data from. The "with" expression tells SQL Server how to return the details of the XML, and the sp_xml_removedocument extended stored procedure removes the XML representation referred to by @id from memory.

The output from running the SQL batch shown above looks like this:



Proc #6: xp_availablemedia

As you might've guessed, if an SQL Server isn't setup and configured properly, then several extended stored procedures can compromise the stability of that server. The xp_availablemedia extended stored procedure as well as a couple of others can do just that.

The xp_availablemedia extended stored procedure is used to return a list of available storage devices that can be written to. It doesn't require any special signature definitions, because all you have to do to use it is call it directly, with no parameters.

Use the following line to get a list of all available storage devices:

exec master..xp_availablemedia

On my SQL Server, the results set looked like this:



Proc #7: xp_getnetname

The xp_getnetname extended stored procedure returns the NetBIOS name of the current system. It accepts one optional parameter and its signature looks like this:

xp_getnetname [@netname [var]char(MAX_COMPUTERNAME_LENGTH_ OUT]

To print out the NetBIOS name of my SQL Server, I used the xp_getnetname extended stored procedure like this:

declare @netname varchar(20)

exec master..xp_getnetname @netname output

print 'The name of my SQL Server is ' + @netname

On my system, the output from the SQL code above is "The name of my SQL Server is SQLSERVER".

Next: Examples (contd.) >>
 

More SQL Server Articles
More By Joe O'Donnell

Comments On: Extended Stored Procedures: Intro And 10 Cool Examples

>>> Be the FIRST to comment on this article!