Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query XML Value Expand / Collapse
Author
Message
Posted Wednesday, January 15, 2014 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 05, 2014 8:50 PM
Points: 1, Visits: 19
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
Post #1531217
Posted Wednesday, January 15, 2014 10:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:23 AM
Points: 283, Visits: 1,110
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
Post #1531228
Posted Thursday, January 23, 2014 10:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
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)



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1534192
Posted Thursday, January 23, 2014 11:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1534212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse