Querying specific values in column that's using XML data

Question

Querying specific values in column that's using XML data

c_wager

SSC Enthusiast

Points: 105
More actions
November 16, 2017 at 7:16 am

#340552

Boy, I've been researching this subject, but I am just not getting it. Sorry if I'm asking a question that has all ready been asked a million times, but its hard to understand when you're a noob like me, and the values in my tables aren't like the others I've seen. So here it goes...
In my SQL Server database, I have a table that has all my listings in it, called ItemsEbay.
(The main identifier for each individual item has a column named ItemID.)
Within the ItemsEbay table is a column named ItemSpecifics that contains XML data. Within the ItemSpecifics XML Data, is a node with a <Name> of UPC, and text and random <value> for that node:
What I would like, is a query that would allow me to search all the items in the ItemsEbay table that have a specific UPC "value" of my choosing, such as TEST10, or 10U100 for instance.
When the query finds the matching values I'm looking for, I would like to be able to replace them all at once with a new value "Does Not Apply."
Any help would be greatly appreciated.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 1

You need XQuery to do this. You can search a series of XML documents in rows and then update then. It's not easy or pretty.

This article might help: http://www.sqlservercentral.com/articles/XML/67548/

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

c_wager - Thursday, November 16, 2017 7:16 AM
Boy, I've been researching this subject, but I am just not getting it. Sorry if I'm asking a question that has all ready been asked a million times, but its hard to understand when you're a noob like me, and the values in my tables aren't like the others I've seen. So here it goes...
In my SQL Server database, I have a table that has all my listings in it, called ItemsEbay.
(The main identifier for each individual item has a column named ItemID.)
Within the ItemsEbay table is a column named ItemSpecifics that contains XML data. Within the ItemSpecifics XML Data, is a node with a <Name> of UPC, and text and random <value> for that node:
What I would like, is a query that would allow me to search all the items in the ItemsEbay table that have a specific UPC "value" of my choosing, such as TEST10, or 10U100 for instance.
When the query finds the matching values I'm looking for, I would like to be able to replace them all at once with a new value "Does Not Apply."
Any help would be greatly appreciated.

This is trivial stuff but one cannot use pictures of data to provide examples, can you please post a sample XML and the expected results!
😎

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Steve Jones - SSC Editor - Thursday, November 16, 2017 7:52 AM
It's not easy or pretty.

Everything is easy if you know it and beuty is in the eye of the beholder😉
😎

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 4

It's definitely not pretty, and doesn't perform well. Learning XQuery isn't easy, either.

You may think it's easy now, Eirikur, but I suspect you're discounting your efforts in the past to get here.

Thom A SSC Guru Points: 99133 More actions · Answer 5

I have to agree with Steve, I'm not a fan of XQuery. I (normally) have to look up the syntax each and every time I have to use it.

To the OP, this might get you on the right path. I'm not quite sure what you want as your WHERE, but the syntax to get you on track is there:

CREATE TABLE #XML (XMLData xml);
INSERT INTO #XML
VALUES (
'<SelectedValues>
 <SelectedValue>
   <Name>UPC</Name>
   <Value>Test10</Value>
 </SelectedValue>
 <SelectedValue>
  <Name>Brand</Name>
   <Value>HP</Value>
 </SelectedValue>
 <SelectedValue>
  <Name>UPC</Name>
   <Value>Test11</Value>
 </SelectedValue>
</SelectedValues>'
)
GO
SELECT SV.S.value('(Value/text())[1]','varchar(10)') AS TextValue
FROM #XML X
  CROSS APPLY X.XMLData.nodes('/SelectedValues/SelectedValue') SV (s)
WHERE SV.S.value('(Name/text())[1]','varchar(10)') = 'UPC';
GO
DROP TABLE #XML;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

c_wager SSC Enthusiast Points: 105 More actions · Answer 6

Thom A - Thursday, November 16, 2017 8:47 AM
I have to agree with Steve, I'm not a fan of XQuery. I (normally) have to look up the syntax each and every time I have to use it.
To the OP, this might get you on the right path. I'm not quite sure what you want as your WHERE, but the syntax to get you on track is there:
CREATE TABLE #XML (XMLData xml);
INSERT INTO #XML
VALUES (
'<SelectedValues>
<SelectedValue>
<Name>UPC</Name>
<Value>Test10</Value>
</SelectedValue>
<SelectedValue>
<Name>Brand</Name>
<Value>HP</Value>
</SelectedValue>
<SelectedValue>
<Name>UPC</Name>
<Value>Test11</Value>
</SelectedValue>
</SelectedValues>'
)
GO
SELECT SV.S.value('(Value/text())[1]','varchar(10)') AS TextValue
FROM #XML X
CROSS APPLY X.XMLData.nodes('/SelectedValues/SelectedValue') SV (s)
WHERE SV.S.value('(Name/text())[1]','varchar(10)') = 'UPC';
GO
DROP TABLE #XML;

For Where, I'm only wanting to look at the values assigned to the UPC node.
('/SelectedValues/SelectedValue[Name="UPC" and Value[contains(.,"TEST10")] ]') = 1 ))

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

Steve Jones - SSC Editor - Thursday, November 16, 2017 8:35 AM
It's definitely not pretty, and doesn't perform well. Learning XQuery isn't easy, either.
You may think it's easy now, Eirikur, but I suspect you're discounting your efforts in the past to get here.

It's better than XPath which I used back in 1999/2000 until 2007/8 when XQuery came out. In fact I think XQuery is as not that bad, the SQL Server implementation is not perfect though.
😎

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 8

c_wager - Thursday, November 16, 2017 1:30 PM
Thom A - Thursday, November 16, 2017 8:47 AM
I have to agree with Steve, I'm not a fan of XQuery. I (normally) have to look up the syntax each and every time I have to use it.
To the OP, this might get you on the right path. I'm not quite sure what you want as your WHERE, but the syntax to get you on track is there:
CREATE TABLE #XML (XMLData xml);
INSERT INTO #XML
VALUES (
'<SelectedValues>
<SelectedValue>
<Name>UPC</Name>
<Value>Test10</Value>
</SelectedValue>
<SelectedValue>
<Name>Brand</Name>
<Value>HP</Value>
</SelectedValue>
<SelectedValue>
<Name>UPC</Name>
<Value>Test11</Value>
</SelectedValue>
</SelectedValues>'
)
GO
SELECT SV.S.value('(Value/text())[1]','varchar(10)') AS TextValue
FROM #XML X
CROSS APPLY X.XMLData.nodes('/SelectedValues/SelectedValue') SV (s)
WHERE SV.S.value('(Name/text())[1]','varchar(10)') = 'UPC';
GO
DROP TABLE #XML;
For Where, I'm only wanting to look at the values assigned to the UPC node.
('/SelectedValues/SelectedValue[Name="UPC" and Value[contains(.,"TEST10")] ]') = 1 ))

How about posting the sample data I've asked for?
😎

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

Thom A - Thursday, November 16, 2017 8:47 AM
I have to agree with Steve, I'm not a fan of XQuery. I (normally) have to look up the syntax each and every time I have to use it.

That's like using the Hungarian Dictionary
😎

c_wager SSC Enthusiast Points: 105 More actions · Answer 10

Eirikur Eiriksson - Thursday, November 16, 2017 2:22 PM
Thom A - Thursday, November 16, 2017 8:47 AM
I have to agree with Steve, I'm not a fan of XQuery. I (normally) have to look up the syntax each and every time I have to use it.
That's like using the Hungarian Dictionary
😎

Heres a sample of XML data from a single item, pulled from ItemSpecifics column found in the ItemsEbay table..
<SelectedValues> <SelectedValue> <Name>Type</Name> <Value>DDR2 SDRAM</Value> </SelectedValue> <SelectedValue> <Name>Form Factor</Name> <Value>DIMM</Value> </SelectedValue> <SelectedValue> <Name>Brand</Name> <Value>Unbranded</Value> </SelectedValue> <SelectedValue> <Name>MPN</Name> <Value>Does Not Apply</Value> </SelectedValue> <SelectedValue> <Name>Number of Pins</Name> <Value>240</Value> </SelectedValue> <SelectedValue> <Name>Bus Speed</Name> <Value>PC2-5300P (667Mhz)</Value> </SelectedValue> <SelectedValue> <Name>Number of Modules</Name> <Value>32</Value> </SelectedValue> <SelectedValue> <Name>Speed</Name> <Value>5300P</Value> </SelectedValue> <SelectedValue> <Name>Capacity per Module</Name> <Value>4 GB</Value> </SelectedValue> <SelectedValue> <Name>UPC</Name> <Value>Does Not Apply</Value> </SelectedValue> </SelectedValues>

and here's data from another item in the table..
<SelectedValues> <SelectedValue> <Name>UPC</Name> <Value>100050</Value> </SelectedValue> <SelectedValue> <Name>Brand</Name> <Value>Fusion-Io</Value> </SelectedValue> <SelectedValue> <Name>MPN</Name> <Value>EP001193-000</Value> </SelectedValue> </SelectedValues>

As far as expected results, I want to be able to find and replace the <value> for the UPC nodes, if any of the values matches my search term.

c_wager SSC Enthusiast Points: 105 More actions · Answer 11

Using this....
SELECT ItemSpecifics.query('/SelectedValues/SelectedValue[Name="UPC" and Value[contains(.,"00")] ]') AS Found FROM ItemsEbay;

works at returning what I'm looking for, but it also returns blank rows on the items that don't match the query, so I have several thousand blank rows..

I would also like to see the corresponding ItemID column for each product.

Thom A SSC Guru Points: 99133 More actions · Answer 12

So, with the data provided, what results are you expecting?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 13

Quick suggestion
😎

USE TEEST; GO SET NOCOUNT ON;

DECLARE @TBL_XML TABLE ( TX_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,TX_XML XML NOT NULL );

DECLARE @TXML01 XML = '<SelectedValues> <SelectedValue> <Name>Type</Name> <Value>DDR2 SDRAM</Value> </SelectedValue> <SelectedValue> <Name>Form Factor</Name> <Value>DIMM</Value> </SelectedValue> <SelectedValue> <Name>Brand</Name> <Value>Unbranded</Value> </SelectedValue> <SelectedValue> <Name>MPN</Name> <Value>Does Not Apply</Value> </SelectedValue> <SelectedValue> <Name>Number of Pins</Name> <Value>240</Value> </SelectedValue> <SelectedValue> <Name>Bus Speed</Name> <Value>PC2-5300P (667Mhz)</Value> </SelectedValue> <SelectedValue> <Name>Number of Modules</Name> <Value>32</Value> </SelectedValue> <SelectedValue> <Name>Speed</Name> <Value>5300P</Value> </SelectedValue> <SelectedValue> <Name>Capacity per Module</Name> <Value>4 GB</Value> </SelectedValue> <SelectedValue> <Name>UPC</Name> <Value>Does Not Apply</Value> </SelectedValue> </SelectedValues>'; DECLARE @TXML02 XML = '<SelectedValues> <SelectedValue> <Name>UPC</Name> <Value>100050</Value> </SelectedValue> <SelectedValue> <Name>Brand</Name> <Value>Fusion-Io</Value> </SelectedValue> <SelectedValue> <Name>MPN</Name> <Value>EP001193-000</Value> </SelectedValue> </SelectedValues>';

INSERT INTO @TBL_XML(TX_XML) SELECT @TXML01 UNION ALL SELECT @TXML02 UNION ALL SELECT @TXML01 UNION ALL SELECT @TXML02 UNION ALL SELECT @TXML01 UNION ALL SELECT @TXML02;

SELECT TX.TX_ID ,SELVAL.DATA.query('*') ,SELVAL.DATA.value('(Name/text())[1]','VARCHAR(50)') AS Name ,SELVAL.DATA.value('(Value/text())[1]','VARCHAR(50)') AS Value FROM @TBL_XML TX OUTER APPLY TX.TX_XML.nodes('SelectedValues/SelectedValue') SELVAL(DATA);