Feeds:
Posts
Comments

Archive for the ‘New Features’ Category

Interesting one today:

In the past, we’ve seen multiple posts on XML DML covering several XML manipulation capabilities. Today, we’ll cover another aspect of XQuery : Ability to add multiple conditions within the same XQuery path.

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
ID INT NOT NULL IDENTITY(1,1)
, XML_Col XML
)
GO

--
-- Create XML value
--
DECLARE   @X XML = '

				Marco
				Polo

				Hello 16
				World 16

				Hello 17
				World 17

				Hello

'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

The sample XMl looks like this:

XMLDML_MultipleConditions

And in it we’ll be querying the following things:

  1. Using Single Condition
    1. Query the number of ParentElement under Root
    2. Query the number of ParentElement with @Att1 = “Color”
      1. Answer: 1
    3. Query the number of ParentElement with @Att1 = “Brand”
      1. Answer: 2
    4. Retrieve element text of ParentElement with @Att1 = “Brand” for Child1
      1. Answer: Hello 16
  2. Using Two conditions
    1. Retrieve element text of ParentElement with @Att1 = “Brand” and @Att2=”17″ for Child1
      1. Answer: Hello 17

XMLDML_MultipleConditions_Detailed.png

--
-- XML Query to retrieve with multiple conditions in XQuery<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
SELECT	  XML_Col.value('count(/Root/ParentElement)', 'INT')													AS [NumberOf_ParentElements]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Color"])', 'INT')										AS [NumberOf_ParentElements_Color_Attribute]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Brand"])', 'INT')										AS [NumberOf_ParentElements_Brand_Attribute]

		, XML_Col.value('(/Root/ParentElement[@Att1="Brand"]/Child1/text())[1]', 'VARCHAR(30)')					AS [Child1_Text_in_Brand]
		, XML_Col.value('(/Root/ParentElement[@Att1="Brand" and @Att2="17"]/Child1/text())[1]', 'VARCHAR(30)')	AS [Child1_Text_in_Brand_and_17]

FROM dbo.SampleXML
GO

Output looks like this:

XMLDML_MultipleConditions_Output

 

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »

Quick one today:

Extended Events make it easy to capture deadlock details into an XEL file, that has deadlock data in XML format.

This XEL file could be imported into a Sql table using sys.fn_xe_file_target_read_file function; It is similar to ::fn_trace_gettable function to load Sql trace file into a table.


SELECT * INTO dbo.DeadlocksXML
FROM sys.fn_xe_file_target_read_file ('I:\Deadlocks\xml_deadlock_report_0_131771103583570000.xel', null, null, null)
GO

MSDN also has an article on this function.

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

Earlier, while troubleshooting deadlocks, we needed a quick way to count the number of victims & processes in each deadlock incident. Since deadlock information is available in XML, we could use COUNT function to measure the occurrences.

Deadlock_Counts

In the past, we’ve covered some techniques to uncover details from deadlock XML data.

XML provides many tools to parse XML files. Below query makes it easy to gather such counts at each level; Be it Victim count or processes count, etc

SELECT
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT') AS [Count_Victims]
FROM dbo.DeadlocksXMLTable
GO

We could send XML Path we want to count as a parameter to COUNT function to gather the number.

DeadLockXML.value(‘count(/event/data/value/deadlock/victim-list/victimProcess)’, ‘INT‘)
Below is an expanded TSQL query that includes more columns from deadlock XML file.

SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2')																		AS [TimeStamp]
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT')										AS [Count_Victims]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'VARCHAR(100)')							AS [Victim1]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[2]', 'VARCHAR(100)')							AS [Victim2]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[3]', 'VARCHAR(100)')							AS [Victim3]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[4]', 'VARCHAR(100)')							AS [Victim4]
FROM dbo.DeadlocksXMLTable
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In the recent past, we’ve seen several XML posts. Today, we’ll cover a way to check if an element text is empty. Let’s take a sample XML file

XMLDML_Exists_Sample

Lets check if the Child2 element in Brand ParentElement is empty or not

XMLDML_EmptyElement

--
-- Check if an element is empty.<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
DECLARE   @X XML = '
        
            
                Marco
                Polo
            
            
                Hello
                
            
            
                Hello
            
        '
--SELECT @X.value('(/Root/ParentElement[@Att1="Brand"]/Child1)[1]', 'VARCHAR(20)')
IF	(
		SELECT @X.value('(/Root/ParentElement[@Att1="Brand"]/Child2)[1]', 'VARCHAR(20)')
	) = ''
	PRINT 'Empty'
ELSE
	PRINT 'Not Empty'

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting one today:

XML DML is one powerful tool in Sql Server arsenal. In that value() function allows us to parse the XMl file and retrieve the values of attributes, elements, text, etc from the XML.

Using the sample XML below, we’ll explore various versions of using value() function.

Examples:

This is the sample XML file

XML_DML_Structure.PNG

DECLARE @X XML = '

....
'

SELECT @X.value('(/StoreConfigurations/ProcessingOptions/PasswordExpirationDays)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>[1]', 'VARCHAR(2)') AS [Returns30]
	, @X.value('(/StoreConfigurations/ProcessingOptions/PasswordExpirationDays)[2]', 'VARCHAR(2)') AS [ReturnsNULL]

	, @X.value('(/StoreConfigurations/ReceiptTexts/ReceiptText/ReceiptHeaderLine1)[1]', 'VARCHAR(100)') AS [Returns YourStore]
	, @X.value('(/StoreConfigurations/ReceiptTexts/ReceiptText[@StoreNumber="6"]/ReceiptHeaderLine2)[1]', 'VARCHAR(100)') AS [Returns Your Street]

	, @X.value('(/StoreConfigurations/Hosts/Host/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 01]
	, @X.value('(/StoreConfigurations/Hosts/Host[2]/RetryCounter)[2]', 'VARCHAR(2)') AS [Returns 04]
	, @X.value('(/StoreConfigurations/Hosts/Host[3]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 03]

	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 01]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/RetryCounter)[2]', 'VARCHAR(2)') AS [Returns 04]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 03]

	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns X]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns Y]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns Z]

--/ReceiptHeaderLine1
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/HostERCUse)[1]', 'VARCHAR(2)') = 'X')
	PRINT 'Yes, its X'
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/HostERCUse)[1]', 'VARCHAR(2)') = 'Y')
	PRINT 'Yes, its Y'
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/HostERCUse)[1]', 'VARCHAR(2)') = 'Z')
	PRINT 'Yes, its Z'
GO

Output:

The result looks like this:

XML_DML_Value

Also we could PRINT from the value function too:

XML_DML_Value_Print.PNG

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Sequence is nice new feature in Sql Server, it allows a single location to manage IDENTITY-ies. In the past, we looked at one aspect of Sequence, today we’ll cover a bit more ground.

Once the Sequence definition is set, you may need to alter the definition. Below sample scripts show a way to modify some aspects of Sequence definition.

--
--   Create SEQUENCE and play with its DEFINITION
--
CREATE SEQUENCE SampleSequence
	AS BIGINT
	START WITH 0
	INCREMENT BY 1
	MINVALUE -10
    MAXVALUE 200
GO

SELECT name, start_value, maximum_value, current_value
FROM sys.sequences
WHERE name = 'SampleSequence' ;
GO

ALTER SEQUENCE SampleSequence RESTART WITH 2
GO

ALTER SEQUENCE SampleSequence MAXVALUE 300;
GO

SELECT name, start_value, maximum_value, current_value
FROM sys.sequences
WHERE name = 'SampleSequence' ;
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Frequently, we run into situations, where we need to put place holders, in a SQL script, that take different values based on some conditions or  settings. Essentially having variables about the script itself (and not the code)

We are taking about variables at the meta-level (script file level, and not the variables in the code itself)

SQLCMD mode within SSMS allows us to set placeholders / variables that will take new values (as you provide) right before executing the script.

First enable SQLCMD mode in SSMS:

SSMS_SQLCMD

In the query window, write some code as below:

--
--  Set variables in a script
--
:SETVAR DatabaseName "master"
:SETVAR TableName "MSreplication_options"

USE $(DatabaseName)
SELECT TOP 10 *
FROM syscolumns

USE $(DatabaseName)
SELECT *
FROM sys.objects
WHERE name LIKE '$(TableName)'
GO

SSMS_SQLCMD_Result

Observations:

Variable replacement occurs both in

USE $(DatabaseName)

and

WHERE name LIKE ‘$(TableName)

  • Having single quotes does not affect variable replacement, as this replacement occurs at the script file level (and node code level — if that makes sense).
  • Since this is a universal replacement capability, it allows you to use variable replacement anywhere in the code without need for special escape characters.
Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »