Below XML query is taking too long, any idea about best approach to get the value
[Trip Advisor Review Count] = CAST(CAST(PPT.PartnerContents AS XML).query('/Contents/Content[@Name=''Trip Advisor Review Count'' and @SectionId=''1'']/text()[1]') as nvarchar(max))
So the data in that column is already varchar then you convert it to xml then query it. That is why. Why not just use basic string manipulation functions? Ppease post the data exactly as it is in the column
Please post the xml by adding the three code ticks before and after it. You don't expect us to type in all that in order to work out a solution for you ?
Here is one tick for you, put three before and after the xml
`
<Contents>
<Content Id="62" Name="Partner main image" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000628741_original">210095</Content>
<Content Id="60" Name="Partner image2" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="61" Name="Partner image3" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Hay alianzas que hacen la fuerza y el vino elaborado en las Bodegas de Fuente Reina es fiel prueba de ello. Andalucía pone el arte, Sevilla las tierras propicias para el cultivo de la vid, y los enólogos Óscar Zapke e Íñigo Manso de Zúñiga su sabiduría en la creación de caldos vinícolas de alta alcurnia. El resultado de la fusión de estos tres factores es un vino que hechizará tus sentidos.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Todo el año, excepto septiembre. Todos los días (11h-17h).</Content>
<Content Id="18" Name="How to book" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">En la Sierra Norte de Sevilla y a 98 km de Sevilla</Content>
<Content Id="37" Name="Marketing Region Classification" SectionId="1" IsMandatory="false" UIType="Text Box" Type="Field">Sur</Content>
<Content Id="110" Name="TOP Partner" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="62" Name="Partner main image" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">210095</Content>
<Content Id="60" Name="Partner image2" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="61" Name="Partner image3" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Hay alianzas que hacen la fuerza y el vino elaborado en las Bodegas de Fuente Reina es fiel prueba de ello. Andalucía pone el arte, Sevilla las tierras propicias para el cultivo de la vid, y los enólogos Óscar Zapke e Íñigo Manso de Zúñiga su sabiduría en la creación de caldos vinícolas de alta alcurnia. El resultado de la fusión de estos tres factores es un vino que hechizará tus sentidos.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Todo el año, excepto septiembre. Todos los días (11h-17h).</Content>
<Content Id="18" Name="How to book" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">En la Sierra Norte de Sevilla y a 98 km de Sevilla</Content>
<Content Id="37" Name="Marketing Region Classification" SectionId="2" IsMandatory="false" UIType="Text Box" Type="Field">Sur</Content>
<Content Id="110" Name="TOP Partner" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="224" Name="Internal Notes" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">2019 production - B6 - Content checked</Content>
</Contents>
Am I going blind or am I not seeing trip advisor count at all in this xml?
hahaha....find attached new one
<Contents>
<Content Id="62" Name="Partner main image" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="Other000361383_original">175246</Content>
<Content Id="60" Name="Partner image2" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="Other000361380_original">175245</Content>
<Content Id="61" Name="Partner image3" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="Other000361387_original">175247</Content>
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Il Country Hotel Poggiomanente è un antico mulino del Seicento trasformato in una villa finemente arredata e decorata. Le camere sono dotate di aria condizionata, TV Sat e ogni comfort. La struttura dispone di sala convegni, piscina, ping-pong ed un ampio parco esterno con zona giochi per bambini. Senza tralasciare il ristorante, le cui sale sono ricavate all'interno di un'antica stazione di posta del Cinquecento.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Accedi al tuo account e verifica le date disponibili.</Content>
<Content Id="34" Name="Marketing OnSite Activities" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="18" Name="How to book" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="110" Name="TOP Partner" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="141" Name="Trip Advisor Review Count" SectionId="1" IsMandatory="false" UIType="Number Box" Type="Field">NaN</Content>
<Content Id="120" Name="Header Title" SectionId="2" IsMandatory="false" UIType="Text Box" Type="Field" />
<Content Id="114" Name="Web Title" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="62" Name="Partner main image" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="Other000361383_original">175246</Content>
<Content Id="60" Name="Partner image2" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="Other000361380_original">175245</Content>
<Content Id="61" Name="Partner image3" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="Other000361387_original">175247</Content>
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Il Country Hotel Poggiomanente è un antico mulino del Seicento trasformato in una villa finemente arredata e decorata. Le camere sono dotate di aria condizionata, TV Sat e ogni comfort. La struttura dispone di sala convegni, piscina, ping-pong ed un ampio parco esterno con zona giochi per bambini. Senza tralasciare il ristorante, le cui sale sono ricavate all'interno di un'antica stazione di posta del Cinquecento.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Accedi al tuo account e verifica le date disponibili.</Content>
<Content Id="34" Name="Marketing OnSite Activities" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="18" Name="How to book" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="110" Name="TOP Partner" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="141" Name="Trip Advisor Review Count" SectionId="2" IsMandatory="false" UIType="Number Box" Type="Field">NaN</Content>
</Contents>
<Contents>
<Content Id="62" Name="Partner main image" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000324557_original">119266</Content>
<Content Id="60" Name="Partner image2" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000323017_original">118801</Content>
<Content Id="61" Name="Partner image3" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000325016_original">119297</Content>
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Situato all'ingresso di Torino, l'Hotel Cairo è un ottimo punto di partenza per scoprire le maggiori attrazioni della città. Perdetevi tra le vie che conducono a Piazza San Carlo con i suoi caffè storici o ritagliatevi un momento per visitare la Reggia Venaria. Al rientro potrete rilassarvi nell'ambiente elegante e raccolto dell'hotel, dove poptrete sorseggiare un drink nel bar presente al suo interno, e al mattino gusterete la colazione a buffet ricca di torte e biscotti fatti in casa.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Accedi al tuo account e verifica le date disponibili</Content>
<Content Id="34" Name="Marketing OnSite Activities" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="18" Name="How to book" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Metro : Lingotto
Tram : Giambone - Linea 4</Content>
<Content Id="37" Name="Marketing Region Classification" SectionId="1" IsMandatory="false" UIType="Text Box" Type="Field">Nord ed estero</Content>
<Content Id="110" Name="TOP Partner" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="120" Name="Header Title" SectionId="2" IsMandatory="false" UIType="Text Box" Type="Field" />
<Content Id="114" Name="Web Title" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="62" Name="Partner main image" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">119266</Content>
<Content Id="60" Name="Partner image2" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">118801</Content>
<Content Id="61" Name="Partner image3" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">119297</Content>
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Situato all'ingresso di Torino, l'Hotel Cairo è un ottimo punto di partenza per scoprire le maggiori attrazioni della città. Perdetevi tra le vie che conducono a Piazza San Carlo con i suoi caffè storici o ritagliatevi un momento per visitare la Reggia Venaria. Al rientro potrete rilassarvi nell'ambiente elegante e raccolto dell'hotel, dove poptrete sorseggiare un drink nel bar presente al suo interno, e al mattino gusterete la colazione a buffet ricca di torte e biscotti fatti in casa.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Accedi al tuo account e verifica le date disponibili</Content>
<Content Id="34" Name="Marketing OnSite Activities" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="18" Name="How to book" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Metro : Lingotto
Tram : Giambone - Linea 4</Content>
<Content Id="37" Name="Marketing Region Classification" SectionId="2" IsMandatory="false" UIType="Text Box" Type="Field">Nord ed estero</Content>
<Content Id="110" Name="TOP Partner" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="141" Name="Trip Advisor Review Count" SectionId="1" IsMandatory="false" UIType="Number Box" Type="Field">816</Content>
<Content Id="141" Name="Trip Advisor Review Count" SectionId="2" IsMandatory="false" UIType="Number Box" Type="Field">816</Content>
</Contents>
Find another example with data
so this data is contained in the column PartnerContents which has a datatype of nvarchar or varchar? What date type is PartnerContents ? And how many rows are we talking about on the table that contains the column PartnerContents ?
Data type of PartnerContents Column is text.
[PartnerContents] [text] NULL,
Currently this table contains 377205 records
are there any indices on that table?
No, none is there
you got few options
PartnerContents column into xml data type column instead of text. This way you can eliminate the CAST(CAST(PPT.PartnerContents AS XML) section which is eating up resources and takes about over 6 minutes. If you cannot do that. the following will shave off 5 minutes--This uses sample data to emulate your huge table
use sqlteam
go
IF OBJECT_ID('dbo.trippy','U') IS NOT NULL
DROP TABLE dbo.trippy
create table dbo.trippy( PartnerContents text null);
insert into dbo.trippy
--5 seconds to create mock data
SELECT TOP 377205
PartnerContents = '<Contents>
<Content Id="62" Name="Partner main image" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000324557_original">119266</Content>
<Content Id="60" Name="Partner image2" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000323017_original">118801</Content>
<Content Id="61" Name="Partner image3" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" ImageName="ES000325016_original">119297</Content>
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Situato allcasa.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Accedi al tuo account e verifica le date disponibili</Content>
<Content Id="34" Name="Marketing OnSite Activities" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="18" Name="How to book" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field">Metro : Lingotto
Tram : Giambone - Linea 4</Content>
<Content Id="37" Name="Marketing Region Classification" SectionId="1" IsMandatory="false" UIType="Text Box" Type="Field">Nord ed estero</Content>
<Content Id="110" Name="TOP Partner" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="1" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="1" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="120" Name="Header Title" SectionId="2" IsMandatory="false" UIType="Text Box" Type="Field" />
<Content Id="114" Name="Web Title" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="62" Name="Partner main image" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">119266</Content>
<Content Id="60" Name="Partner image2" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">118801</Content>
<Content Id="61" Name="Partner image3" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field">119297</Content>
<Content Id="24" Name="Logo Image (Marketing Partner)" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="58" Name="Partner Description" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Situato all.</Content>
<Content Id="35" Name="Marketing Opening dates" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Accedi al tuo account e verifica le date disponibili</Content>
<Content Id="34" Name="Marketing OnSite Activities" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="18" Name="How to book" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="23" Name="Location" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field">Metro : Lingotto
Tram : Giambone - Linea 4</Content>
<Content Id="37" Name="Marketing Region Classification" SectionId="2" IsMandatory="false" UIType="Text Box" Type="Field">Nord ed estero</Content>
<Content Id="110" Name="TOP Partner" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="108" Name="Theme Picto" SectionId="2" IsMandatory="false" UIType="Image Uploader" Type="Field" />
<Content Id="113" Name="Web Text" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="33" Name="Marketing NearBy" SectionId="2" IsMandatory="false" UIType="Text Area" Type="Field" />
<Content Id="141" Name="Trip Advisor Review Count" SectionId="1" IsMandatory="false" UIType="Number Box" Type="Field">' + cast(ISNULL(RAND(CHECKSUM(NEWID())),0) as varchar(50)) + '</Content></Contents>'
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
--The way you are doing it took 00:06:39 NOT GOOD
select [Trip Advisor Review Count] = CAST(CAST(PPT.PartnerContents AS XML).query('/Contents/Content[@Name=''Trip Advisor Review Count'' and @SectionId=''1'']/text()[1]') as nvarchar(max))
From dbo.trippy PPT
--This takes ~ 1.34 still NOT GOOD but might work for you.
IF OBJECT_ID('tempdb..#TripAdvisorStats','U') IS NOT NULL
DROP TABLE #TripAdvisorStats
create table #TripAdvisorStats(PartnerContents xml not null)
insert into #TripAdvisorStats
select PartnerContents = cast(isnull(PPT.PartnerContents,'') as xml)
From dbo.trippy PPT
select PartnerContents.value('(/Contents/Content)[31]', 'nvarchar(max)') as [Trip Advisor Review Count]
FROM #TripAdvisorStats
--This one takes about 1 minute also, FAST but OOGLY
select dommbo, substring(dommbo,0, CHARINDEX('<', dommbo, 0))
From (
select substring(
SUBSTRING(PartnerContents, charindex('Name="Trip Advisor Review Count"', PartnerContents,0), len(cast(PartnerContents as varchar(max))) ),
charindex('>',SUBSTRING(PartnerContents, charindex('Name="Trip Advisor Review Count"', PartnerContents,0) -1, len(cast(PartnerContents as varchar(max))) ), 0), 1500) as dommbo
From dbo.trippy ) PPT
I converted with columns as XML so that there is no need to cast, can you help me transform below query to get value without casting. It just fetching one record
[Trip Advisor Review Count] = cast(cast(PPT.PartnerContents as xml).query('/Contents/Content[@Name=''Trip Advisor Review Count'' and @SectionId=''1'']/text()[1]') as nvarchar(max)),Also,
If i try to run below query i am getting Error 'The xml data type cannot be selected as DISTINCT because it is not comparable.
'
[Trip Advisor Review Count] = (PPT.PartnerContents).query('/Contents/Content[@Name=''Trip Advisor Review Count'' and @SectionId=''1'']/text()[1]'),