Case Sensitivity in XML Extraction (Part II)

    In my previous post I showed an elegant way to extract case sensitive data from an XML, but what if we'd like to use that method to extract all case sensitive data?

Here is how we can do it. 

At first here is our test setup:

DECLARE @xml XML = '
<ROOT>
    <ELEMENT NAME="ElementName" VALUE="PascalCase" />
    <ELEMENT NAME="ELEMENTNAME" VALUE="UPPERCASE" />
    <ELEMENT NAME="elementname" VALUE="LOWERCASE" />
</ROOT>';

SELECT @xml;

Here is our query, which would extract ONLY a single value:

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Case sensitive via parameter'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT[@NAME=sql:variable("@RequestElement")]') AS X(C)

It returns a single row for only exact match for "ElementName" value:

Now I will change that query to disregard case sensitivity:

DECLARE @RequestElement SYSNAME = 'ElementName';
DECLARE @RequestElement_ci SYSNAME = UPPER(@RequestElement);

SELECT [Case] = 'Case-insensitive parameter'
    , [@RequestElement] = @RequestElement
    , [@RequestElement_ci] = @RequestElement_ci
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT[upper-case(@NAME)=sql:variable("@RequestElement_ci")]') 
AS X(C)

In order to extract all values, disregarding of case sensitivity or collation we just switched both values to an Upper case.

Case Sensitivity in XML Extraction

I recently encountered an interesting issue where XML elements had similar names but differed in character capitalization.

Lets show it via an example:

At first, will declare an XML variable and assign the simplest XML code to it:

DECLARE @xml XML = '
<ROOT>
    <ELEMENT NAME="ElementName" VALUE="PascalCase" />
    <ELEMENT NAME="ELEMENTNAME" VALUE="UPPERCASE" />
    <ELEMENT NAME="elementname" VALUE="LOWERCASE" />
</ROOT>';

SELECT @xml;

Now will try to extract from that XML element with a name "ElementName":

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Not case sensitive'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT') AS X(C)
WHERE C.value('@NAME', 'VARCHAR(100)') = @RequestElement;

That query returned all three elements, while I expected only a single one:

That happened because SQL Server is not case sensitive by default.
In order to return the record I need I may switch the whole database I'm working in to Case Sensitive Collation or just simply add a Case Sensitive collation to my WHERE clause like this:

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Case sensitive using Collation'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT') AS X(C)
WHERE C.value('@NAME', 'VARCHAR(100)') = @RequestElement COLLATE Latin1_General_CS_AS;

That code returned me the only a single line I want:

Do you think the case is closed?

Not at all — there’s an alternative solution that may be even more elegant:

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Case sensitive via parameter'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT[@NAME=sql:variable("@RequestElement")]') AS X(C)

It also returns a single row, but without the need to worry about collation:

In this final example I've used my SQL variable "@RequestElement" as a parameter inside of an XML query, which clearly demonstrates the differentially between use of "WHERE" clause and XML node parameter:

- Filtering in the "WHERE" clause depends on SQL Server database collation, but an XML query is always case sensitive.

SSIS: REPLACENULL does not support (DT_DBTIMESTAMP2,7)

 Using the "REPLACENULL" functionality frequently in the "Derived Column" component, the "Conditional Split" component, and other places in SSIS where formulas can be applied is common.

However, I recently encountered an issue with the "DT_DBTIMESTAMP2" data type.

The following formula produced an error:

REPLACENULL(TestDt, (DT_DBTIMESTAMP2,7)"1900-01-01 00:00:00.0000000")

Error: 0xC020902A at Test Transformation, Derived Column [2]: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[TestDt]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This error occurs because "REPLACENULL" returns a "DT_WSTR" data type. To make it work, we need to convert "DT_DBTIMESTAMP2" to "DT_WSTR" and then convert it back to "DT_DBTIMESTAMP2", like this:

(DT_DBTIMESTAMP2,7)REPLACENULL((DT_WSTR,30)TestDt,"1900-01-01 00:00:00")

Alternatively, a more elegant solution is to replace "REPLACENULL" with an "IF" condition:

ISNULL(TestDt)?(DT_DBTIMESTAMP2,7)"1900-01-01 00:00:00":TestDt

SSIS warning message about SSAS Cube processing: "Operation completed with XXX problems logged"

If you process SSAS cubes via SSIS packages you might notice a weird message like "Full Processing:Warning: Server: Operation completed with XXX problems logged."

How you can get that message (if you have that problem):

1. You can do a report, from your package's execution and get something like this:

2. You can run a T-SQL script against your SSIS server:

SELECT TOP 100 message_time, message
FROM [SSISDB].[internal].[operation_messages]
WHERE message_type = 110 AND message_source_type = 40
   AND message LIKE '%Warning: Server: Operation completed with%problems logged.'
ORDER BY message_time DESC;

If you have that problem you might have something like this:

The Problem.

1. SSIS Server does not provide you any details on that warning nor any associated problem.

2. SSAS Server also does not report any problems associated with that Cube processing.

Solution.

You can use "Extended Events" to capture these problems:

1. For that, you have to create an Extended Event Session using following script on your SSAS Server

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ObjectDefinition>
    <Trace>
      <ID>SSAS_CubeProcessing_Stream</ID>
      <Name>SSAS_CubeProcessing_Stream</Name>
      <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
        <event_session name="SSAS_CubeProcessing_Stream" dispatchLatency="0" maxEventSize="0" maxMemory="4096" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <event package="AS" name="ProgressReportError" />
          <target package="package0" name="event_stream" />
        </event_session>
      </XEvent>
    </Trace>
  </ObjectDefinition>
</Create>

2. Then you run "Watch Live Data" for that session

3. Run your SSIS Cube processing package and monitor the events.

As the result you'll get something like this:

A Problem description you can find in an Event's details under "TextData" name:

At the end, do not forget to delete your Extended Events monitoring Session.