Posts tagged ‘T-SQL’

  |  
January 3, 2011

T-SQL: Parsing data type xml

If you only occasionally dabble in XML and XPath utilizing the XML data type in SQL Server can be a bit daunting. I found it very useful in passing parameters to stored procedures containing object graphs. The same can be accomplished the table parameter if you only have a list of one type of objects, but if you need to pass an object graph of parent/child objects of different types, using XML will be your best bet.

Below I demonstrate how one could represent a list of Contact objects in an XML document and subsequently access the data via a select statement. the @Contacts parameter could be used as a parameter to a stored procedure if you’re running SQL 2005 or higher. Use your imagination for the rest.

declare @Contacts xml

set @Contacts =
    ‘<root>
    <Contact>
        <ContactID>1</ContactID>
        <Email>contact1@contact1.com</Email>
    </Contact>
    <Contact>
        <ContactID>2</ContactID>
        <Email>contact2@contact2.com</Email>
    </Contact>
    <Contact>
        <ContactID>3</ContactID>
        <Email>contact3@contact3.com</Email>
    </Contact>
    </root>’

select
    ContactID = Contact.value(‘ContactID[1]‘, ‘int’),
    Email = Contact.value(‘Email[1]‘, ‘varchar(255)’)
from
    @Contacts.nodes(‘root/Contact’) as Contacts([Contact])

Posted in T-SQL | Leave a Comment »

Tags: , ,
November 26, 2010

T-SQL coalescing column values across rows

Recently, I came across a requirement to take a resultset containing NULL fields and fill a column’s NULL value with the last known non-null value of the given column. This is best illustrated via an example.

If we have the results:

n
–
1
null
null
2
null
null
null
null
3
4

The desired output would be:

1
1
1
2
2
2
2
3
4

After testing a few methodologies, the fastest solution utilized an update with a variable.

Suppose, we have this table definition:

declare @mytable table (n int)

The statement to “fill the gaps” would be:

– this declaration should match the column type containing NULLs
declare @n int
set @n = null — by default it will be NULL, but this is a good practice

update @mytable set @n = coalesce(n, @n), n = coalesce(n, @n)

To explain, the above statement assigns a new value to the variable @n if the current row’s “n” column is not null. Otherwise it assigns the value of @n. Next it will assign the value of @n to column “n”, IF column “n” is null. This solution will scan every row (in the order of insertion into your table or based on a clustered index if one exists) and replace NULL values in the column of your choice with the last non-NULL value in the sequence of values.

Review the definition of COALESCE in Books Online if you’re not clear.

Posted in T-SQL | Leave a Comment »

Tags: ,
  |  
Follow

Get every new post delivered to your Inbox.