3

We're trying to filter a set of XML based on a value we provide.

We have the following XML in an XML field with our database, and if passing through the number "5052095050830", we need to find this specific node in the XML. The number we provide may exist any number of times.

Can any body provide some example SQL to assist?

Thanks

<Attributes>
  <ProductVariantAttribute ID="4387">
    <ProductVariantAttributeValue>
      <Value>5052095050830</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="9999">
    <ProductVariantAttributeValue>
      <Value>5052095050830</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4388">
    <ProductVariantAttributeValue>
      <Value>104401330A</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4389">
    <ProductVariantAttributeValue>
      <Value>6905</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4390">
    <ProductVariantAttributeValue>
      <Value>6906</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4391">
    <ProductVariantAttributeValue>
      <Value>Monday, October 27, 2008</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
</Attributes>
ajbrun
  • 161
  • 2
  • 13

3 Answers3

3

You can use the .exist() method - something like this:

SELECT 
(list of columns) 
FROM
dbo.YourTable
WHERE
YourXmlColumn.exist('//Value[text()="5052095050830"]') = 1

This checks against that particular value you've supplied. The more precisely you can define the XPath where that value is expected to be found, the better for your performance.

YourXmlColumn.exist('//Value[text()="5052095050830"]') = 1

is pretty bad - it looks into every single <Value> node anywhere in the XML to find that value.

Something like this:

YourXmlColumn.exist('/Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value[text()="5052095050830"]') = 1

would be much more focused and thus much better for performance - but it would only those those particular nodes defined by that very XPath statement

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks - I'm pretty much there now. I'm trying to get something like this to work. This is coming up with either zero results. What am I doing wrong? `DECLARE @Barcode VARCHAR SELECT @Barcode = '5052095050830' SELECT * FROM Nop_ProductVariantAttributeCombination WHERE AttributesXml.exist('/Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value[text()="sql:variable(@Barcode)"]') = 1` – ajbrun Apr 15 '11 at 13:24
  • 2
    @Adrian: not sure- but you should try with the double quotes around the `sql:variable`: `/Value[text()=sql:variable(@Barcode)] ` - also you might want to give your `@Barcode` variable a length when declaring it - otherwise it's `VARCHAR(1)`, I believe... – marc_s Apr 15 '11 at 14:03
0

If you want to retrieve the attribute @ID here it is:

        DECLARE @xml AS XML
    SET @xml =
        '<Attributes>
        <ProductVariantAttribute ID="4387">
            <ProductVariantAttributeValue>
                <Value>5052095050830</Value>
            </ProductVariantAttributeValue>
        </ProductVariantAttribute>
        <ProductVariantAttribute ID="9999">
            <ProductVariantAttributeValue>
                <Value>5052095050830</Value>
            </ProductVariantAttributeValue>
        </ProductVariantAttribute>
        <ProductVariantAttribute ID="4388">
            <ProductVariantAttributeValue>
                <Value>104401330A</Value>
            </ProductVariantAttributeValue>
        </ProductVariantAttribute>
        <ProductVariantAttribute ID="4389">
            <ProductVariantAttributeValue>
                <Value>6905</Value>
        </ProductVariantAttributeValue>
        </ProductVariantAttribute>
        <ProductVariantAttribute ID="4390">
            <ProductVariantAttributeValue>
                <Value>6906</Value>
            </ProductVariantAttributeValue>
            </ProductVariantAttribute>
        <ProductVariantAttribute ID="4391">
            <ProductVariantAttributeValue>
                <Value>Monday, October 27, 2008</Value>
        </ProductVariantAttributeValue>
        </ProductVariantAttribute>
    </Attributes>'


    select 
    t.x.value('@ID' ,'varchar(50)') ProductVariantAttributeID
    from @xml.nodes('//ProductVariantAttribute') t(x)
    where t.x.value('(ProductVariantAttributeValue/Value)[1]' , 'nvarchar(50)') = '5052095050830'
hkravitz
  • 1,345
  • 1
  • 10
  • 20
0

This returns the values as another XML, I'm not sure how you wanted it.

DECLARE @xml AS XML
SET @xml =
'<Attributes>
  <ProductVariantAttribute ID="4387">
    <ProductVariantAttributeValue>
      <Value>5052095050830</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="9999">
    <ProductVariantAttributeValue>
      <Value>5052095050830</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4388">
    <ProductVariantAttributeValue>
      <Value>104401330A</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4389">
    <ProductVariantAttributeValue>
      <Value>6905</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4390">
    <ProductVariantAttributeValue>
      <Value>6906</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
  <ProductVariantAttribute ID="4391">
    <ProductVariantAttributeValue>
      <Value>Monday, October 27, 2008</Value>
    </ProductVariantAttributeValue>
  </ProductVariantAttribute>
</Attributes>'


SELECT @xml.query
('
    for $text in //Attributes/ProductVariantAttribute
    where $text/ProductVariantAttributeValue/Value[text()] = 5052095050830
    return string($text/@ID)
')
SQLMason
  • 3,275
  • 1
  • 30
  • 40