Transact SQL – String Splitting Using XML

Background

Unfortunately splitting a String into rows was lacking in Transact SQL for a while.

MSFT changed that in Version 2016.

Split_String is capable and handy.

Here is the link.

 

Prior to v2016

Prior to Version 2016, there are many tools out in the wild for accomplishing same.

There are CLR Functions.  There are functions that use dbo.Numbers

 

XML

And, there are functions that rely on XML’s inherent ability to split an input into nodes.

 

XML

Standing On

Found a nice working model on sqlhints.com.

BTW, sqlHints.com is Basavaraj Biradar.

And, here is the particular post that we will be using.

It is titled “How to Split comma or any other character delimited string into a Table in Sql Server.”

 

Objective

  1. Look for separator
    • Replace separator with endNode and beginNode
      • Close out endNode
      • Start new node with beginNode
  2. Begin & End fragment
    • Begin Fragment with beginNode
    • End Fragment with endNode
  3. Start and end node fragment
    • XML needs root node
      • And, so we start string with rootNode
      • And, end with rootNode
  4. Use XQuery to parse node
    • Parse node using XQuery
    • Nodes ( “//node”)

Code

[stringSplit].[itvf_stringSplitUsingXML]

 


use [master]
go


if schema_id('stringSplit') is null
begin

	exec('create schema [stringSplit] authorization [dbo] ')

end
go

if object_id('[stringSplit].[itvf_stringSplitUsingXML]') is null
begin

	exec
	(
		'create function [stringSplit].[itvf_stringSplitUsingXML]
			()
			RETURNS @RESULT TABLE
			(
				Value VARCHAR(MAX)
			)
		as
		begin

			return

		end

		'
	) 

end
go


ALTER FUNCTION [stringSplit].[itvf_stringSplitUsingXML]
(
      @string  VARCHAR(MAX)
	, @separator CHAR(1) = ','
)
RETURNS @tblResult TABLE
(

	  [rowNumber] smallint not null identity(1,1)
	, [value]	  VARCHAR(600)
	, [xml]		  xml
	, [element]	  xml

)
AS
BEGIN

	/*

		a) sqlhints.com
		   http://sqlhints.com/tag/split-comma-separated-values-in-sql/
	
	*/    

	 DECLARE @xml XML
	 DECLARE @xmlAsString nvarchar(max)

	 declare @rootBegin varchar(10)
	 declare @rootEnd varchar(10)

	 declare @node      varchar(10)
	 declare @nodeBegin varchar(10)
	 declare @nodeEnd varchar(10)

	 declare @xmlNodeAsString as varchar(600)

	 set @rootBegin = '<root>'
	 set @rootEnd = '</root>'

	 set @node = 'node'
	 set @nodeBegin = '<node>'
	 set @nodeEnd = '</node>'

	 /*

		Look for separator, when found
			end earlier node by replacing separator
				 with [nodeEnd] 
				 and starting new node with [nodeBegin]

	 */
	 set @xmlNodeAsString = REPLACE
								(
									  @string
									, @separator
									, @nodeEnd + @nodeBegin
								)

	/*
		Bracket fragment with begin and end node
	*/
 	 SET @xmlNodeAsString =  @nodeBegin 		
							+ @xmlNodeAsString
							+ @nodeEnd

	/*
		Root node
		 separated out contents
		 End Root Node
	*/
 	 SET @xmlAsString = @rootBegin
							+ @xmlNodeAsString
							+ @rootEnd

	/*
		Convert to XML
	*/
	set @xml = @xmlAsString
 

	INSERT INTO @tblRESULT
	(
		  [value]
		, [xml]
		, [element]

	)
	SELECT 

		  [value]
		  = 
			(
				t.i.value
				(
						'(.)[1]'
					, 'varchar(max)'
				)
			)

		, [xml]
			= @xml

		 , [element]
		  = 
			(
				t.i.query
				('.')
			)
	  
	from @xml.nodes('//node') AS t(i)

	RETURN

END
go


 

Invoke

 


	declare @data varchar(600)

	set @data = 'sammie,bobbie,jackie'

	select *

	from   [master].[stringSplit].[itvf_stringSplitUsingXML]
			(
				  @data
				, default
			)

 

Output

SQL Server – Extended Events – Querying Data – Day 1

Background

Once we have our Extended Events Session Setup and it is collecting data, we should occasionally check in and review the collected data.

 

Sample

XML Data

 

Query

Outline

We have a sample query below, here is what it does:

  1. XEL & XEM
    • It sets the @fileXEL variable to the folder and file signature for our XEL files
    • It sets the @fileXEM variable to the folder and file signature for our XEM files
  2. It creates a Common Table Expression (CTE)
    • invokes sys.fn_xe_file_target_read_file
      • Passes along the XEL and XEM file signatures
      • It discard records that have “UPDATE STATISTICS
  3. It uses XPATH and breaks the record using //event
  4. Parses data
    • XML Fragment
      • query(‘.’)
        • Sample data
          • <event name=”rpc_completed” package=”sqlserver” id=”64″ version=”1″ timestamp=”2017-04-01T16:14:45.363Z”><data name=”cpu”><value>0</value><text /></data></event>
    • timestamp
      • value(‘@timestamp’, ‘datetimeoffset’)
        • Sample Data
          • 2017-04-01 16:14:45.3630000 +00:00
    • collect_system_time
      • .query(‘/event/action[@name=”collect_system_time”]/value/text()’)
        • Sample Data
          • 131355368858528137
      • .query(‘/event/action[@name=”collect_system_time”]/text/text()’)
        • Sample Data
          • 2017-04-01T16:14:45.852Z
    • session_id
      • .query(‘/event/action[@name=”session_id”]/value/text()’)
        • Sample Data
          • 54
    • client_host_name
      • .query(‘/event/action[@name=”client_hostname”]/value/text()’)
        • Sample Data
          • LAB-DB-SQL
    • username
      • .query(‘/event/action[@name=”username”]/value/text()’)
        • Sample Data
          • tomw
    • client_app_name
      • .query(‘/event/action[@name=”client_app_name”]/value/text()’)
        • Sample Data
          • Report Server
    • sql_text
      • .query(‘/event/action[@name=”sql_text”]/value/text()’)

 

Code


declare @fileXEL sysname
declare @fileXEM sysname


--set @fileXEL = 'E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance_0_131356693200370000.xel'
set @fileXEL = 'E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance*.xel'
set @fileXEM = 'E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance*.xem'

; with cte
(
	  [objectName] 
	, [eventData] 
)
as
(
	SELECT top 10 

		  [objectName] = tblFNXE.[object_name]
		, [eventData] = cast(tblFNXE.event_data as xml)

	FROM sys.fn_xe_file_target_read_file
		(
			  @fileXEL
			, @fileXEM
			, null
			, null
	   ) tblFNXE

	where tblFNXE.event_Data not like '%UPDATE STATISTICS%'

)
select 

		  [objectName] = cte.[objectName]

		, [eventData] = cast(cte.eventData as xml)
		, [XMLFragment] = [event].[node].query('.')

		--timestamp on root node
		, [tsAsDateTime] = [event].[node].value('@timestamp', 'datetimeoffset')		

		--collect system time
		, [systemTSAsValue] = ([event].[node].query('/event/action[@name="collect_system_time"]/value/text()'))
		, [systemTSAsText] = ([event].[node].query('/event/action[@name="collect_system_time"]/text/text()'))

		--session ID
		, [sessionID] = [event].[node].query('/event/action[@name="session_id"]/value/text()')

		-- client Host Name
		, [clientHostName] = [event].[node].query('/event/action[@name="client_hostname"]/value/text()')

		-- username
		, [username] = [event].[node].query('/event/action[@name="username"]/value/text()')

		-- client App Name
		, [clientAppName] = [event].[node].query('/event/action[@name="client_app_name"]/value/text()')

		-- SQL Text
		, [sqlText] = [event].[node].query('/event/action[@name="sql_text"]/value/text()')

from   cte

cross apply cte.eventData.nodes(N'//event') event(node)



Output

Informatica – PowerCenter Express – Datatype – XML

Background

As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.

 

Table Structure

Here is our table structure.

MSSQL

Admin-CourseManagerUserLog-MSSQL

MySQL

Admin-CourseManagerUserLog-MYSQL

 

Informatica

Original Table Structure

Physical Data Object – Admin.CourseManagerUserLog

PhysicalDataObject

Explanation:

Later on, was cognizant that our XML Column, details, precision or size is 0

 

Mappings

Mappings

Explanation:

Later on, noticed that for the details column, the arrow is not full.

 

Mappings - Later

 

Run Mappings

Run the mapping.

And, things ran well.  Was not until later that I found that the details column on the destination, MySQL, was empty.

 

Create View on the Source

Here is a workaround.

In the view, add new columns to track the unsupported XML Column.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[Admin].[CourseManagerUserLog_XMLColumnConverted]') is null
begin

	exec('create view [Admin].[CourseManagerUserLog_XMLColumnConverted] as select 1/0 as [shell] ')

end
go

alter view [Admin].[CourseManagerUserLog_XMLColumnConverted]
as

SELECT
		    [id]
		  , [username]
		  , [resourceID]
		  , [status]
		  , [message]
		  , [details]
		  , [detailsAsVarcharMax]
				= cast(	[details] as varchar(max))	
		  , [detailsAsVarchar]
				= cast(	[details] as varchar(8000))	
		  , [userID]
		  , [refID]
		  , [record_created]
		  , [appointmentID]

from   [Admin].[CourseManagerUserLog]
GO

GRANT SELECT ON [Admin].[CourseManagerUserLog_XMLColumnConverted] TO [public]

go

 

Informatica

Resource

Resource

Explanation

  1. details, XML Column, comes in xml.  But, size is 0
  2. detailsAsVarchar, varchar column, size is 8000 ( max size in MS SQL Server )
  3. detailsAsVarcharMax [varchar(max) ], size is 0, as well

 

Mappings

After AutoLink

After AutoLink ….

Mappings-After-Autolink

Those columns with matching names are Auto Linked.

 

After Column – detailsAsVarchar is linked to details

And, then we manually linked the columns whose names are not matched.

Here in:

  1. detailsAsVarchar (MSSQL) right-arrow details (MySQL)

Mappings - Later - Details - 0448PM