CODEWITHSUNDEEP
sql-serverxml-parsing
sam
sam

Reputation: 1304

XML exist method not filtering records in SQL Server 2012

I have 2 questions:

  1. I am trying to filter attribute @Type = "Bikes" in the below example. But I am getting all 6 rows. I should get only 3 rows. Why exist not working here, I have no Idea?

  2. How to filter further @Type = "Bikes" and Product = "Mountain"

My code mentioned below:

declare @xml xml = '<StoreSurvey>
    <AnnualSales>800000</AnnualSales>
    <AnnualRevenue>80000</AnnualRevenue>
    <BankName>United Security</BankName>
    <BusinessType>BM</BusinessType>
    <YearOpened>1996</YearOpened>
    <Specialty>Mountain</Specialty>
    <SquareFeet>21000</SquareFeet>
    <Brands>2</Brands>
    <Internet>ISDN</Internet>
    <NumberEmployees>13</NumberEmployees>
    <Products Type="Bikes">
      <Product>Mountain</Product>
      <Product>Road</Product>
      <Product>Racing</Product>
    </Products>
    <Products Type="Clothes">
      <Product>Jerseys</Product>
      <Product>Jackets</Product>
      <Product>Shorts</Product>
    </Products>
  </StoreSurvey>'

Code for question 1:

  select T.col.value('.','varchar(100)')  
  from @xml.nodes('/StoreSurvey/Products/Product') as T(col)
  where T.col.exist('/StoreSurvey/Products[@Type = "Bikes"]') =1

Desired output:

Mountain
Road
Racing

Code for question 2: this code is causing "syntax error":

  select T.col.value('.','varchar(100)')  
  from @xml.nodes('/StoreSurvey/Products/Product') as T(col)
  where T.col.exist('/StoreSurvey/Products[@Type = "Bikes"]/[Product = "Mountain"]') = 1

Upvotes: 1

Views: 48

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89101

For both of those, use a query form like this:

with q as
(
  select products.col.value('@Type','varchar(100)')  ProductType, 
         product.col.value('.','varchar(100)') Product
  from @xml.nodes('/StoreSurvey/Products') as products(col)
  cross apply products.col.nodes('Product') as product(col)
)
select * 
from q
  where ProductType = 'Bikes'
    and Product = 'Mountain';

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453327

Both of these return your desired first result

  select T.col.value('.','varchar(100)')  
  from @xml.nodes('/StoreSurvey/Products/Product') as T(col)
  where T.col.exist('..[@Type = "Bikes"]') =1

The second one seems more natural as it is strange to get all Product nodes then back up and apply a filter on the parent axis (with ..)

  select T.col.value('.','varchar(100)')  
  from @xml.nodes('/StoreSurvey/Products[@Type = "Bikes"]/Product') as T(col)

It is not clear what your exact desired results are for Q2 but this returns a result

select T.col.value('.','varchar(100)')  
from @xml.nodes('/StoreSurvey/Products[@Type = "Bikes"]/Product[text() = "Mountain"]') as T(col)

Upvotes: 2

Related Questions