Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

XQuery for the SQL Server DBA: Math & Strings

FrustrationUp until now we’ve covered path statements and namespaces to retrieve values from XML within SQL Server.  It’s pretty common once you start pulling values from any data source that you’ll need to do some math or string manipulations against them.  Let’s tackle that today.

I’ll first show you how to do it with XQuery and then demonstrate that just because you can, doesn’t mean you should.

XQuery can get really verbose so I’ve removed the hitters from my sample document to focus on Pitchers without extra predicates or namespaces to filter them out.  See previous posts if you need a refresher on those topics.

declare @x xml 
SET @x = '
<fantasy>
  <player Position="P">
    <name>Justin Verlander</name>
    <team>Tigers</team>
    <era>3.41</era>
    <k>1671</k>
    <innings>1772</innings>
  </player>  
  <player Position="P">
    <name>Clayton Kershaw</name>
    <team>Dodgers</team>
    <era>2.60</era>
    <k>1206</k>
    <innings>1180</innings>
  </player>
</fantasy>'

Arithmetic

XQuery supports arithemtic operations using the same xpath operators you would use in the filter predicates demonstrated earlier.  See link for all operators supported.

  • Addition:            +
  • Subtraction:       
  • Multiplication:    *
  • Division:            div
  • Modulo:             mod

Using / as the division operator would obviously cause problems for XML

In the following example I query the @x variable I set above to retrieve the strikeouts per inning for both Kershaw and Verlander.

SELECT 'Clayton Kershaw' AS Pitcher,
   @x.query('
   (fantasy/player/name[text()="Clayton Kershaw"]/../k/text())[1] 
   div
   (fantasy/player/name[text()="Clayton Kershaw"]/../innings/text())[1]
   ') AS [Strikeout per Inning]
UNION ALL
SELECT 'Justin Verlander' AS Pitcher,
   @x.query('
   (fantasy/player/name[text()="Justin Verlander"]/../k/text())[1] 
   div
   (fantasy/player/name[text()="Justin Verlander"]/../innings/text())[1]
   ') AS [Strikeout per Inning]

Produces the following result:

K per Inning

There are a couple items worth noting in the query above.

1: In order to apply arithmetic to two seperate path statements they must be formatted to satisfy SQL Server that each path will return a singleton value.  Don’t forget XPath will ALWAYS return the sequence of all values that match.  Applying a [1] predicate to the end of each satisfies this requirement.  Otherwise you get:

Msg 2389, Level 16, State 1, Line 24
XQuery [query()]: ‘div’ requires a singleton (or empty sequence)

2: You cannot mix a UNION and the XML data type without the ALL option.  SQL Server will not compare XML data types to ensure they are distinct.

Msg 421, Level 16, State 1, Line 20
The xml data type cannot be selected as DISTINCT because it is not comparable.

3: Don’t forget you can use the value function instead to specify the data type returned.

SELECT 'Clayton Kershaw' AS Pitcher,
   @x.value('
   (fantasy/player/name[text()="Clayton Kershaw"]/../k/text())[1] 
   div
   (fantasy/player/name[text()="Clayton Kershaw"]/../innings/text())[1]
   ','decimal(9,2)') AS [Strikeout per Inning]
UNION ALL
SELECT 'Justin Verlander' AS Pitcher,
   @x.value('
   (fantasy/player/name[text()="Justin Verlander"]/../k/text())[1] 
   div
   (fantasy/player/name[text()="Justin Verlander"]/../innings/text())[1]
   ','decimal(9,2)') AS [Strikeout per Inning]

String Manipulation

Aside from mathmatical operations it’s not uncommon to need to do some string manipulation. I can get the first letter of Kershaw’s team using the substring function that is part of the XQuery definition.

SELECT 'Clayton Kershaw' AS Pitcher,
   @x.value(
     'substring((/fantasy/player[2]/team/text())[1],1,1)'
     ,'char(1)'
   ) AS [Team Letter]

There are a variety of other string manipulation options defined by the W3C such as substring-after, substring-before but they are not included in the subset supported by SQL Server.  It seems your limited to substring alone.

Remind me why I care?

The reality is that you can perform both math and string manipulation within TSQL to get these same results.  The reason I took the time to show you that you could do it within XQuery is to remind you that XQuery is a language of it’s own.  XQuery is not a subset of TSQL.  It’s important that you remember XQuery has it’s own syntax, rules, structure, and odd behaviors.

Scientific Notation

Grant Fritchey’s ring buffer query inspired my original interest in this topic.  While modifying his query to present the data in a little different format I discovered yet another extremely frustrating behavior of XQuery.  Take a look at the simplified version of Grant’s query below.

WITH RingBuffer
  AS 
  ( 
   SELECT CAST(dorb.record AS XML) AS xRecord, dorb.timestamp
   FROM     sys.dm_os_ring_buffers AS dorb
   WHERE    dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
  )
SELECT  
  DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) 
      AS RmDateTime ,
  xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') 
      AS TotalPhysicalMemory ,
  xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') 
      AS AvailablePhysicalMemory ,
  xr.value('(MemoryRecord/TotalPhysicalMemory)[1] -
            (MemoryRecord/AvailablePhysicalMemory)[1]',
           'real') AS UsedPhysicalMemory -- WHY REAL ?? 
FROM    RingBuffer AS rb
  CROSS APPLY rb.xRecord.nodes('Record') record ( xr )
  CROSS JOIN sys.dm_os_sys_info AS dosi
ORDER BY RmDateTime DESC;

You might wonder why I’m using a real data type on that last column when I’m subtracting one bigint from another. Turns out that anytime XPath does math against a number larger than 6 digits it starts returning the results in scientific notation.  Once it passes the result back to TSQL you start seeing an error like this:

Msg 8114, Level 16, State 5, Line 13
Error converting data type nvarchar to bigint.

To accommodate a conversion from scientific notation I have to resort to a real (a float also works).  But at this point, I’m just going to go back to TSQL where it all makes sense.  Below I pull the math operator out of the XPath statement, rewrite as two XPath statements and let TSQL handle the math.

WITH RingBuffer
  AS 
  ( 
   SELECT CAST(dorb.record AS XML) AS xRecord ,
   dorb.timestamp
   FROM     sys.dm_os_ring_buffers AS dorb
   WHERE    dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
  )
SELECT  
  DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) 
    AS RmDateTime ,
  xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') 
    AS TotalPhysicalMemory ,
  xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') 
    AS AvailablePhysicalMemory ,
  xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') - 
  xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') 
    AS UsedPhysicalMemory
FROM    RingBuffer AS rb
  CROSS APPLY rb.xRecord.nodes('Record') record ( xr )
  CROSS JOIN sys.dm_os_sys_info AS dosi
ORDER BY RmDateTime DESC;

Tune in next time where we will present more ways to use XQuery (FLWOR) and more reasons to hate it. You can also return to the master post of this series.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on December 16, 2013 by in XPath XQuery XML.
%d bloggers like this: