Query XML Value

Question

Post reply

Query XML Value

bryan 83518

SSC Journeyman

Points: 91
More actions
January 15, 2014 at 10:34 am

#99867

Trying to work out how to query xml that looks like this I will loop through all the artist but only want the Main Artist not the Secondary
<Artists>
<ArtistText>Genie Zhuo</ArtistText>
<Artist Type="Main">
<FullName>Genie Zhuo</FullName>
</Artist>
<Artist Type="Secondary">
<FullName>The Second Man</FullName>
</Artist>
</Artists>
<Artists>
<ArtistText>The Man</ArtistText>
<Artist Type="Main">
<FullName>The Man</FullName>
</Artist>
<Artist Type="Secondary">
<FullName>The Second Man</FullName>
</Artist>
</Artists>
This query works but if I change the [1] to 2 or 4 I get the secondary artist
SELECT
XMLContents.value('(/Product/Artists/Artist/FullName)[1]', 'nvarchar(max)') as RecordType
FROM [XMLFile]
where XMLFileId = 1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 1

Rewritten to get the xml from a variable, but you should be able to apply this logic to your situation:

declare @x xml

select @x = '

<Artists>

<ArtistText>Genie Zhuo</ArtistText>

<Artist Type="Main">

<FullName>Genie Zhuo</FullName>

</Artist>

<Artist Type="Secondary">

<FullName>The Second Man</FullName>

</Artist>

</Artists>

<Artists>

<ArtistText>The Man</ArtistText>

<Artist Type="Main">

<FullName>The Man</FullName>

</Artist>

<Artist Type="Secondary">

<FullName>The Second Man</FullName>

</Artist>

</Artists>

'

SELECT

a.value('(./FullName)[1]', 'nvarchar(max)') as RecordType

FROM @x.nodes('//Artists/Artist[@Type eq "Main"]') y(a)

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Richard Fryar (1/15/2014)
Rewritten to get the xml from a variable, but you should be able to apply this logic to your situation:
declare @x xml
select @x = '
<Artists>
<ArtistText>Genie Zhuo</ArtistText>
<Artist Type="Main">
<FullName>Genie Zhuo</FullName>
</Artist>
<Artist Type="Secondary">
<FullName>The Second Man</FullName>
</Artist>
</Artists>
<Artists>
<ArtistText>The Man</ArtistText>
<Artist Type="Main">
<FullName>The Man</FullName>
</Artist>
<Artist Type="Secondary">
<FullName>The Second Man</FullName>
</Artist>
</Artists>
'
SELECT
a.value('(./FullName)[1]', 'nvarchar(max)') as RecordType
FROM @x.nodes('//Artists/Artist[@Type eq "Main"]') y(a)

You can simplify the XPath in your query like this:

SELECT

a.value('(FullName)[1]', 'nvarchar(max)') as RecordType

FROM @x.nodes('Artists/Artist[@Type eq "Main"]') y(a)

or for really simple you could also do this:

SELECT

a.value('(*)[1]', 'nvarchar(1000)') as RecordType

FROM @x.nodes('//*[@Type eq "Main"]') y(a)

... and to really speed up performance you could specify a text() node for your return value like this:

SELECT

a.value('(FullName/text())[1]', 'nvarchar(max)') as RecordType

FROM @x.nodes('Artists/Artist[@Type eq "Main"]') y(a)

-- ...or...

SELECT

a.value('(*/text())[1]', 'nvarchar(1000)') as RecordType

FROM @x.nodes('//*[@Type eq "Main"]') y(a)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

bryan 83518 (1/15/2014)
Trying to work out how to query xml that looks like this I will loop through all the artist but only want the Main Artist not the Secondary
<Artists>
<ArtistText>Genie Zhuo</ArtistText>
<Artist Type="Main">
<FullName>Genie Zhuo</FullName>
</Artist>
<Artist Type="Secondary">
<FullName>The Second Man</FullName>
</Artist>
</Artists>
<Artists>
<ArtistText>The Man</ArtistText>
<Artist Type="Main">
<FullName>The Man</FullName>
</Artist>
<Artist Type="Secondary">
<FullName>The Second Man</FullName>
</Artist>
</Artists>
This query works but if I change the [1] to 2 or 4 I get the secondary artist
SELECT
XMLContents.value('(/Product/Artists/Artist/FullName)[1]', 'nvarchar(max)') as RecordType
FROM [XMLFile]
where XMLFileId = 1

Richard showed how you could pull the data from a SQL variable. If you had your data in a table you could query the XML like this:

SELECT a.value('(FullName)[1]', 'nvarchar(max)') as RecordType

FROM #sampleXML sx

CROSS APPLY x.nodes('Artists/Artist[@Type="Main"]') y(a)

A couple performance notes:

1st if you are trying to return text (e.g. <xxx>this is text</xxx>) then you want to specify a text node (which I will demonstrate in a moment.)

2nd, it does not appear that you would need nvarchar(max). (max) data types can be killers, for this you should consider something smaller (in my example I used nvarchar(1000).

Here's a test harness:

IF OBJECT_ID('tempdb..#sampleXML') IS NOT NULL DROP TABLE #sampleXML;

CREATE TABLE #sampleXML (xid int primary key, x xml not null)

DECLARE @x varchar(4000)='

<Artists>

<ArtistText>#5</ArtistText>

<Artist Type="Main">

<FullName>Artist #1</FullName>

</Artist>

<Artist Type="Secondary">

<FullName>Artist #2</FullName>

</Artist>

</Artists>

<Artists>

<ArtistText>#6</ArtistText>

<Artist Type="Main">

<FullName>Artist #3</FullName>

</Artist>

<Artist Type="Secondary">

<FullName>Artist #4</FullName>

</Artist>

</Artists>';

DECLARE @rows int=1000;

WITH tally AS

(

SELECT TOP (@rows)

ROW_NUMBER() OVER (ORDER BY (SELECT ($))) AS n

FROM sys.all_columns a CROSS JOIN sys.all_columns b

)

INSERT #sampleXML

SELECT n,

CONVERT(xml,

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@x,

'#1','#'+CONVERT(varchar(1000),floor(900000*rand(convert(varbinary, newid())))+100000)),

'#2','#'+CONVERT(varchar(1000),floor(900000*rand(convert(varbinary, newid())))+100000)),

'#3','#'+CONVERT(varchar(1000),floor(900000*rand(convert(varbinary, newid())))+100000)),

'#4','#'+CONVERT(varchar(1000),floor(900000*rand(convert(varbinary, newid())))+100000)),

'#5','random text:'+CONVERT(varchar(1000),floor(900000*rand(convert(varbinary, newid())))+100000)),

'#6','random text:'+CONVERT(varchar(1000),floor(900000*rand(convert(varbinary, newid())))+100000)))

FROM tally

Here's three versions of the same query using the performance improvements I mentioned along with the results:

-- not using text() node

SELECT a.value('(FullName)[1]', 'nvarchar(max)') as RecordType

FROM #sampleXML sx

CROSS APPLY x.nodes('Artists/Artist[@Type="Main"]') y(a)

-- using text() node

SELECT a.value('(FullName/text())[1]', 'nvarchar(max)') as RecordType

FROM #sampleXML sx

CROSS APPLY x.nodes('Artists/Artist[@Type="Main"]') y(a)

-- changing nvarchar(max) to nvarchar(1000)

SELECT a.value('(FullName/text())[1]', 'nvarchar(1000)') as RecordType

FROM #sampleXML sx

CROSS APPLY x.nodes('Artists/Artist[@Type="Main"]') y(a)

Results:

1,000 rows:

-- without text() node, nvarchar(max):

SQL Server Execution Times:

CPU time = 359 ms, elapsed time = 758 ms.

-- with text() node, nvarchar(max):

SQL Server Execution Times:

CPU time = 374 ms, elapsed time = 601 ms.

--change nvarchar(max) to nvarchar(1000):

SQL Server Execution Times:

CPU time = 203 ms, elapsed time = 532 ms.

10,000 rows:

-- without text() node, nvarchar(max):

SQL Server Execution Times:

CPU time = 3260 ms, elapsed time = 7420 ms.

-- with text() node, nvarchar(max):

SQL Server Execution Times:

CPU time = 2403 ms, elapsed time = 5113 ms.

-- with text() node, nvarchar(1000):

SQL Server Execution Times:

CPU time = 1419 ms, elapsed time = 3209 ms.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001