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)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s