Transact SQL – Get XML Attribute Value

Background

Trying to get an XML Attribute’s value, but ran into error.

XML Fragment

Image

xmlFragment.song.20181225.1026PM

Code


<song id="1" album="control" name="Pleasure Principle" releasedYear="1986" />

Code

Prepare Data

SQL


set nocount on
go

set XACT_ABORT on
go

use [tempdb]
go

declare @tblSong TABLE
(

	  [id] smallint not null identity(1, 1)
	, [songFragment] xml not null
	, [song]         nvarchar(80) null
)

insert into @tblSong
(
  [songFragment]
)
select
 'song id="1" album="control" name="Pleasure Principle" releasedYear="1986" /'

Error

Error – Top-level attribute nodes are not supported

SQL


select 

       tblS.songFragment

    , [song]
        = tblS.songFragment.value
            (
                  '@name'
                , 'sysname'
            )

from   @tblSong tblS

Error

Error – Image

topLevelAttributeNodesAreNotSupported.20181225.1040PM

Error – Textual


Msg 2390, Level 16, State 1, Line 41
XQuery [value()]: Top-level attribute nodes are not supported

Remediation

Datatype – XML – Value Function

Objective

  1. Use /song to track down to the node
  2. We will use [1] to specify the top node
  3. And, use @[attribute-name] to specify specific attribute

SQL

select 

       tblS.songFragment

     , [id]
        = tblS.songFragment.value
            (
                    '(/song[1]/@id)'
                , 'int'
            )

    , [album]
         = tblS.songFragment.value
            (
                    '(/song[1]/@album)'
                , 'sysname'
            )

    , [song]
        = tblS.songFragment.value
            (
                    '(/song[1]/@name)'
                , 'sysname'
            )

    , [releasedYear]
        = tblS.songFragment.value
            (
                    '(/song[1]/@releasedYear)'
                , 'sysname'
            )

from   @tblSong tblS

Datatype – XML – Nodes Function

Objective

  1. Use cross apply nodes (/song) to track down to the node
  2. Value Datatype function
    • Use @[attribute-name] to specify specific attribute
    • And, be sure to cast to our targeted datatype

SQL

select tblS.songFragment

        , [node] = q1.c1.query('.')
        , [album] = q1.c1.value('@album', 'sysname')
        , [song] = q1.c1.value('@name', 'sysname')
        , [releasedYear] = q1.c1.value('@releasedYear', 'int')

from   @tblSong tblS

CROSS APPLY tblS.[songFragment].nodes('/song') AS q1(c1)

XPath code Generator – Web Tools

Background

Wanted to quickly review freely available online tools for generating Xpath from XML documents.

Sample XML Documents

Will use these XML documents as samples :-

  1. Microsoft
    • Docs. / NET / C# Guide / Programming guide / Programming concepts / LINQ
      • Sample XML File: Customers and Orders in a Namespace
        Link

 

Tools

  1. Xmltoolbox
    • XPath Generator
  2. Qutoric
  3. XPather.com

 

In depth

Xmltoolbox

XPath Generator

Author

Xmltoolbox is authored by Ole Bille.

Usage

Outline
  1. Launch web browser
  2. And, go here ( http://xmltoolbox.appspot.com/xpath_generator.html )
  3. Paste the XML text into the canvas
  4. In the loaded canvas, click on the elements you want evaluated
  5. Please review and capture the generated Xpath results

 

Screenshots

xpathgenerator.20181217.0344AM

qutoric

PathEnq

Usage

Outline
  1. Launch web browser
  2. And, go here ( http://www.qutoric.com/xslt/analyser/xpathtool.html )
  3. Invoke the Choose File button at the bottom of the screen
  4. Select the file to be evaluated
  5. In the loaded canvas, click on the elements you want evaluated

 

Screen Shots

xmlSource.20181217.0319AM

xmlSource.20181217.0322AM

 

XPather.com

Author’s Guideline

  1. This web app enables you to query XML/HTML documents with your browser in real time. It can generate queries for you too!
    • You can enter your xpath query in the top-left panel and it will be instantly executed against this document.
    • To generate an xpath query for a specific element, please hold CTRL and hover over it. An xpath is generated.

Usage

To generate an XPath, please paste the XML into the canvas.

Once the XML is available and accessible, please move your cursor to the screen area, hold down the Ctrl button while hovering or selecting the XML tag or body.

Samples

Sample #1

xpather.com.20181217.0308AM

Deficiency

  1. Supports Element, but not attribute

 

 

 

 

 

Xpath Query – Compare Positional Versus Condition

Background

As always too much time on my hands.

I wanted to compare the query generated by XMLToolBox against one I tweaked gently.

Here is the post that introduces XMLToolBox.

 

Lab

Queries

XPath – Position


set nocount on;
go

declare @xml XML

set @xml=
'
<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

'

select

		  [resultValueUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/text/text()[1]' )
						as varchar(30)
				)




 

XPath – Condition


set nocount on;
go

declare @xml XML

set @xml=
'
<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

'



select

		  [resultValueUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/text/text()[1]' )
						as varchar(30)
				)


 

Compare Queries

Let us have the queries together and compare them.

 

Queried Together

Code



set nocount on;
go

declare @xml XML

set @xml=
'
<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

'

select

		  [resultValueUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/text/text()[1]' )
						as varchar(30)
				)




select

		  [resultValueUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/text/text()[1]' )
						as varchar(30)
				)

Query Plan Comparison – Query Inline

 

Query Plan Comparison – Side by Side

Using SQL Server Management Studio (SSMS) v2016, we can run both queries individually and compare the saved Query Plan

Image

Tabulated
Item Position  Condition
 Cached Plan Size  152 KB  192 KB
 CompileCPU  25  118
 CompileMemory  1736  2664
 CompileTime  25  118
 EstimatedSubtreeCost  806.458  410.401
 MemoryGrant  16128  17920
 OptimizationLevel  FULL  FULL
 RetrievedFromCache  true  true

 

 

Execution Time – Comparison

Image

 

Explanation
  1. Consistently the query with hardcoded position came in around 40 ms while the conditional one came in around 160ms

Summary

It appears from this one test, generated queries that rely on an element’s fixed position are less taxing on the system.

Interestingly, positional queries are costed higher.

On the other hand, queries generated via Conditions are likely less performant.

 

 

XPath Query Tools – XmlToolBox

Background

Lately I have been working more and more with Extended Events and prior to that Cached Plans exploration.

And, has both are stored as XML, it makes sense to see what tools are available for accurately querying XML data.

 

Body

Here is the collected event.


<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = 'A' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = 'A' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

 

Googled

Googled on XML Query XPath and found many tools.

In this post we will discuss XMLToolBox.

 

XMLToolBox

I liked XMLToolBox has unlike many other tools one does not have to start with a query, just paste the the XML body unto the canvas, and point at the XML element or attribute you want to generate the query on.

URL

Here is the Link for XMLToolbox.

Usage

Here is what happens when we :

  1. Copy and paste the XML noted above unto the XML field textarea
  2. Find the result element and click on the text element

Analysis

Here is the generated Xpath:

/event[@name=”sql_batch_completed”]/data[7]/text/text()

 

Interpretation

  1. Parse using event ( /event )
  2. Look for the attribute name matching sql_batch_completed
  3. Transverse to the 7th data element
  4. Find the text element within the the 7th element and return the text portion of that element

 

Summary

XMLToolBox is easy to use and capable.

Unfortunately, it lacks flexibility because it is based on the position of elements.

If the schema changes a bit, the XPath might return the wrong element as it would refer to the original position.

 

Notepad++ and XML Editing

Background

I need a way to quickly edit XML within my editor of choice, Notepad++.

What to do, but took to Google and see whether Notepad++ supports XML.

And, yes it does:

How to format XML in Notepad
http://stackoverflow.com/questions/3961217/how-to-format-xml-in-notepad

 

Auto Update

In Notepad++, to install or update plugins, I will access the menu option Plugins/Plugin Manager/”Show Plugin Manager”, select the plugin that I want, and click Install.

PlugInManager

But, no go.

Here is what happens when I try out PowerShell Lexer.

FirewallIssues

 

BTW, “PowerShell Lexer” provides syntax highlighting for Power-Shell.

The problem is a Proxy configuration and authentication issue.  And, the problem merits a far more interesting post, that I will save for another day.

For today, we will trod the laborer’s path and update manually.

XML Tools

Download

Where are the PlugIns?

http://sourceforge.net/projects/npp-plugins/files/

 

Where is the XML Tools PlugIn?

http://sourceforge.net/projects/npp-plugins/files/XML%20Tools/

As of today, the file that we will download will be Xml Tools 2.4.4 Unicode.zip.

 

Installation

Extract

Let us extract the compressed (zip) file

extractedFiles

 

Embed Files

  • Select all the files in the ext_libs folder unto Notepad++ home folder; in our case C:\Program Files (x86)\Notepad++
  • Copy xmltools under the main binary file (notepad++)

 

Review New Folder

Here is what our main line folder looks like:

mainFolder

 

Plugins

Here is the XMLTools.dll in the plugins folder:

XMLTools_Subdir

 

Verify

Re-launched Notepad++ and tried to encode XML.

Here are the options:

NoXMLEncodeUnderXMLTools

Here are some of the options under XML Tools:

  • Check XML syntax now
  • Validate now
  • Convert selection XML to text
  • Convert selection text to XML
  • Comment selection
  • Un-Comment selection

 

I am probably wrong, but it does not appear any of the options specifically address XML Encoding.

 

HTML Tools

XML Encode?

Took to Net to see if there is a more specific plugin for XML Encode.

From https://kollegaru.wordpress.com/2012/05/24/notepad-html-tag-plugin-easy-html-encode-decode-functions/, it seems I should try out HTMLTag.

Download

Where is the HTMLTag PlugIn?

http://sourceforge.net/projects/npp-plugins/files/HTMLTag/HTMLTag%20plugin%20v0.50/

HTMLTagFolders

 

Since June 14th, 2012, the HTMLTag Plugin is v.5 and available @ http://sourceforge.net/projects/npp-plugins/files/HTMLTag/HTMLTag%20plugin%20v0.50/

 

 

Installation

Extract

Let us extract the compressed (zip) file

HTMLTagFolderExtracted

 

Embed Files

  • Copy all the folders and files under the Applications’ plug-in folder.

 

Review New Folder

Here is what our main line folder looks like:

HTMLTagFolderAvailed

 

Verify

Again, re-launched Notepad++ and here are the options under Plugins\HTML Tag.

HTMLTagEncodeAndDecodeOptions

 

From the screen above, we are able to encode and decode HTML Entities. Note that same applies to XML.

 

Summary

So it seems both plugins are needed.

The XML Plugin for validating XML, and the HTML Plug-in for encoding and decoding XML/HTML.