Feeds:
Posts
Comments

Posts Tagged ‘value()’

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

 

Advertisements

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:

Recently, we capture deadlock server side traces and needed a quick way to retrieve all SPIDs and procedures involved in deadlocks.

The deadlock trace graph is an XML file. If there was one or two deadlocks, we could amanyze it manually. But when you have several deadlocks (100’s or more) and need to analyze all the instances of deadlock entries in the trace, we need a query to parse the XML tree.

Enter XML DML!!

Sample Deadlock XML file looks like this:

We need to retrieve the procedure name involved in the deadlock. Use value() function to parse through the XML path and retrieve the attribute or element value.

Attribute value using @AttributeName

--
--	Return Attribute value in an XML element
--
SELECT	  CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'VARCHAR(100)')
		, CONVERT(XML, TextData)
FROM [After]
WHERE EventClass = 148

Pay attention the path mentioned — that shows what value to retrieve:

/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[1]

  • ‘/deadlock-list/deadlock/process-list/process/executionStack/frame/’ is the location of the attribute
  • ‘@procname’ is the attribute to retrieve
  • [1] says to retrieve the vale from the first instance of such element — as XML could have multiple repeating similar elements. So we could also say [2], [3]… for respective instances.
  • We mention datatype VARCHAR(30) as a second parameter to value function indicating the type of value we are returning.

Attribute text:

Similarly, we could retrieve text value using XML value function:

--
--	Return XML element value
--
SELECT	  CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[1]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[2]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[3]', 'VARCHAR(100)')
		, CONVERT(XML, TextData)
FROM [After]
WHERE EventClass = 148

Similarly, pay attention the path mentioned — that shows text to retrieve:

‘/deadlock-list/deadlock/process-list/process/executionStack/frame)[1]’

  • /deadlock-list/deadlock/process-list/process/executionStack/frame/ is the location of the text
  • [1] says to retrieve the vale from the first instance of such element — as XML could have multiple repeating similar elements. So we could also say [2], [3]… for respective instances.

For more information, please review MSDN article.

 

Hope this helps,
_Sqltimes

 

Read Full Post »