LINK: http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx

Why write a cursor to traverse each table – this stored proc allows you to traverse each table automatically:

EXECUTE sp_MSforeachtable ‘EXECUTE sp_spaceused [?];’;

Neat!

——————

Its been a while since I’ve last posted. I’ve setup the latest version of WordPress, and blog via email so hopefully I’ll get more motivated to do something about it…Shorter and sweeter is probably the key.

In this issue – selecting unique values using only XPath v1….and I still don’t completely understand it!

Take the following XPath query I (eventually) wrote:

//product_vertical_classification/row[not(product_type_id=preceding-sibling::row/product_type_id)]/product_type_id/text()

Without giving too much away, the following XML snippet was the cause for concern:

<product_vertical_classification>

      <row>

            <product_type_id>SELFCONTAI</product_type_id>

            <product_type_description>Self Contained</product_type_description>

            <product_sub_type_1_id>SCCABPARK</product_sub_type_1_id>

            <product_sub_type_1_description>Park Cabin</product_sub_type_1_description>

            <product_sub_type_lowest>SCCABPARK</product_sub_type_lowest>

      </row>

      <row>

            <product_type_id>SELFCONTAI</product_type_id>

            <product_type_description>Self Contained</product_type_description>

            <product_sub_type_1_id>SELFCONTAI</product_sub_type_1_id>

            <product_sub_type_1_description>Self Contained</product_sub_type_1_description>

            <product_sub_type_lowest>SELFCONTAI</product_sub_type_lowest>

      </row>

      <row>

            <product_type_id>VANCAMP</product_type_id>

            <product_type_description>Caravan/Camping</product_type_description>

            <product_sub_type_1_id>VANCAMP</product_sub_type_1_id>

            <product_sub_type_1_description>Caravan/Camping</product_sub_type_1_description>

            <product_sub_type_lowest>VANCAMP</product_sub_type_lowest>

      </row>

      <row>

            <product_type_id>VANCAMP</product_type_id>

            <product_type_description>Caravan/Camping</product_type_description>

            <product_sub_type_1_id>VCCARAVAN</product_sub_type_1_id>

            <product_sub_type_1_description>Caravan Park</product_sub_type_1_description>

            <product_sub_type_lowest>VCCARAVAN</product_sub_type_lowest>

      </row>

</product_vertical_classification>

I needed to select a unique list of all product_type_id’s in the XML. Given my limited understanding of XML, I resorted to DevX for assistance and here’s my interpretation of what it’s doing:

1.      Select from all product_vertical_classification elements

2.      Select rows where

a.      The product_type_id is NOT equal to the product id of any row BEFORE this one in the XML

3.      Select the text from element product_type_id from the result set

It’s the preceding-sibling axis that threw me off. Pretty clever actually.

——————

The GSA is an expensive and very proprietary search box created by Google.

It works great at search, and seems to be insanely powerful, except for one little problem:

Meta data limits

Maximum number of meta tags that can be returned with getfields: 64.

Maximum number of bytes per meta tag returned, including the name of the meta tag and its contents: 320 bytes.

Maximum number of bytes of meta data returned per search result: 4K bytes.

Oh man….this is going to be a long, loooooong day

——————