Category Archives: SQLXML

Getting maximum consecutive years in T-SQL using Common Table Expressions (CTE)

This is an expansion (and slight variation) of Joe’s answer in StackOverflow regarding the thread Find the maximum consecutive years for each ID’s in a table (Oracle SQL) using SQL Server. I’ve tested this in SQL Server 2014, but should work from SQL Server 2005 onwards where the ranking functions are supported.

The problem Joe solved is not uncommon – i.e. how to get the consecutive years of something (consecutive year sales, consecutive years in school, consecutive years volunteering etc), and his solution is pretty clever.

Let’s assume you are working on a sales database. You have multiple clients who could be purchasing from your store several times a year, or a couple of times every five years. You may want to know what’s the maximum consecutive years they’ve purchased from you. Why? Perhaps in a marketing campaign, you may want to give your loyal customers (purchased in 5 or more consecutive years) a special discount.

Your data may look like the following screenshot. Notice that in this example, Client 00001, 00002 and 00003 purchased only once. Client 00004 purchased several times, and it looks like there were purchases in consecutive years.

Download sample T-SQL script to create and populate the SAMPLESALES table

01 All Revenue

Let’s take it step by step to understand both the problem and solution better. It will be easy to visually identify which clients have purchased consecutive years if we first display all the unique years that client has purchased:

SELECT 
    DISTINCT
    CLIENTID,
    CLIENTNAME,
    YEAR(REVENUEDATE) REVENUEYEAR
FROM
    SAMPLESALES

Here’s the result:

Client 00004

Here we can see Client 00004 purchased in 2004 and 2005 (2 consecutive years), but did not purchase in 2006. Starting 2007, Client 0004 started purchasing again every year until 2012 (6 consecutive years).
Read more

SQLXML: How to get related role ad report from a Blackbaud Task (TASKPECXML)

When working with Blackbaud Enterprise CRM, you may create tasks that point to a report. Here’s how to extract the related report and roles from a task:

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
                     'http://www.w3.org/2001/XMLSchema' AS xsd,
             'bb_appfx_commontypes' AS common,
             'bb_appfx_pagedefinition' AS pagedef,
              DEFAULT 'bb_appfx_task'), 
taskcte AS 
(
SELECT  
   TC.NAME TASKNAME,
   SR.NAME ROLENAME, 
   R.SYSTEMROLEID, 
   TC.TASKSPECXML,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowReport/@ReportID)[1]', 'varchar(100)') REPORTID_FROMTASKSPEC,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowPage/@PageID)[1]', 'varchar(100)') PAGEID 
FROM
    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_TASK R
    INNER JOIN SYSTEMROLE SR
    ON R.SYSTEMROLEID = SR.ID
    INNER JOIN TASKCATALOG TC
    ON R.TASKID = TC.ID
)

SELECT * 
FROM taskcte

Determining Blackbaud Ad Hoc Queries that use Site in Designation

We had to trace which among our hundreds of Blackbaud Enterprise CRM (BBEC) ad hoc queries were using Site from the Designation node. We also needed to figure out if they were being used as output or being used as filter.

Here’s the script that did the trick.
Read more

SQLXML: Fun with SQLXML XQuery – Miscellaneous Functions – Part 1

Just going through some old notes; here are some samples I compiled before that might still be of interest to those of you who are venturing into SQLXML.

The samples below are using miscellaneous numeric and string functions.

Have fun!

Sample XML

[sql]
DECLARE @xml XML
SELECT @xml = ‘
<Employees Dept=”IT”>
<Employee Number=”1001″ Hourly=”11.35″ FullName=”Suzy Jacobs”>
<FName>Suzy</FName>
<LName>Jacobs</LName>
<Phone>111-111-1111</Phone>
<Phone>222-222-2222</Phone>
<Phone>111-222-1111</Phone>
</Employee>
<Employee Number=”10″ FullName=”Bob Willow” ReportsTo=”Steve Wong”/>
<Employee Number=”1003″ FullName=”Tony Carpenter” FName=”Tony” LName=”Carpenter”/>
<Employee Number=”1004″ FullName=”Suzy Jacobs” FName=”Suzy” LName=”Jacobs”/>
<Staff Number=”1003″ FName=”John” FullName=”John Smith” LName=”Smith” Department=”ABC” />
<MoreStaff Number=”1020″ FullName=”Ronnie Bates” FName=”Ronnie” LName=”Bates” Address=”XYZ” Department=”ABC” />
</Employees>’
[/sql]

Read more

SQL Server XML Red Gate Ebook, XQuery Labs

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages! 🙂

SQLXML : How to Work With XML Elements (or Nodes) in SQL Server

Assume this is your XML snippet
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @authorsXML XML

SET @authorsXML = ‘
<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>

[/sql]

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

[sql collapse=”false” firstline=”1″ gutter=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]

(/Author/LastName)[1]

[/sql]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

Read more

SQLXML : How to Merge Two Nodes Using FOR XML PATH

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— ============================================================
— sample code that shows how to merge different nodes
— in the same XML document by using FOR XML PATH()
— Donabel Santos
— ============================================================

— sample XML snippet
DECLARE @sampleXML XML
SET @sampleXML =

<Contacts>
<Contact Type="Main" Value="John Doe">
<Locations>
<Location Type="Headquarters" Address="123 XYZ" City="New Westminster"
Province="BC" Country="CA" PostalCode="V1L1B6" />
</Locations>
<Phones>
<Phone Type="Main" Value="6041112222" />
<Phone Type="Secondary" Value="6041113333" />
<Phone Type="Fax" Value="6045553322" />
</Phones>
<Emails>
<Email Type="Main" Value="john.doe@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
</Emails>
</Contact>

<Contact Type="Secondary" Value="Mary Smith">
<Locations>
<Location Type="Headquarters" Address="123 ABC" City="New Westminster"
Province="BC" Country="CA" PostalCode="V1L1B6" />
</Locations>
<Phones>
<Phone Type="Main" Value="6041112255" />
<Phone Type="Secondary" Value="6041113777" />
<Phone Type="Fax" Value="6045553311" />
</Phones>
<Emails>
<Email Type="Main" Value="mary.smith@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
</Emails>
</Contact>
</Contacts>

— get only the elements underneath <Phones> and <Emails>
SELECT
@sampleXML.query (‘(/Contacts/Contact/Phones/*)’),
@sampleXML.query (‘(/Contacts/Contact/Emails/*)’)
FOR XML PATH(”)

— result
/*
<Phone Type="Main" Value="6041112222" />
<Phone Type="Secondary" Value="6041113333" />
<Phone Type="Fax" Value="6045553322" />
<Phone Type="Main" Value="6041112255" />
<Phone Type="Secondary" Value="6041113777" />
<Phone Type="Fax" Value="6045553311" />
<Email Type="Main" Value="john.doe@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
<Email Type="Main" Value="mary.smith@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
*/

[/sql]

« Older Entries