XQuery Filter like Where clause

Question

I have fetch the data based on specific condition from the XML value. Idea is to have one table with two columns ID and Data(XML dataType). I have to fetch the data for specific ID.

Here is the example and I wish to achieve the result as only first row with Sears Tower ONLY. I am getting two rows.

IF OBJECT_ID('tempdb..#ExistExample') IS NOT NULL
DROP TABLE #ExistExample
GO

CREATE TABLE #ExistExample
(
  XMLID Int,
  XMLDocument xml
)

INSERT INTO #ExistExample
VALUES (100,'<Buildings>
  <Building>
    <Name>Sears Tower</Name>
    <Floor1>Yes</Floor1>
    <Floor2>Yes</Floor2>
    <Floor3>No</Floor3>
  </Building>
  <Building>
    <Name>IDS Building</Name>
      <Floor1>Yes</Floor1>
      <Floor2>Yes</Floor2>
      <Floor3>Yes</Floor3>
  </Building>
</Buildings>')

DECLARE @data varchar(1000)
DECLARE @ID INT
SET @ID = 101
SET @data = 'Sears Tower'

INSERT INTO #ExistExample
VALUES (101,'<Buildings>
  <Building>
    <Name>Sears Tower</Name>
    <Floor1>Yes</Floor1>
    <Floor2>Yes</Floor2>
    <Floor3>No</Floor3>
  </Building>
  <Building>
    <Name>IDS Building</Name>
      <Floor1>Yes</Floor1>
      <Floor2>Yes</Floor2>
      <Floor3>Yes</Floor3>
  </Building>
</Buildings>')

--SELECT * FROM #ExistExample

SELECT 
c.value('(Name/text())[1]','varchar(25)') AS BuildingName,
c.value('(Floor1/text())[1]','varchar(25)') AS Floor1,
c.value('(Floor2/text())[1]','varchar(25)') AS Floor2,
c.value('(Floor3/text())[1]','varchar(25)') AS Floor3
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Buildings/Building') as t(c)
WHERE c.exist('//Building/Name[.=sql:variable("@data")]') = 1
AND XMLID = @ID

Please consider registering an account so that you can edit your questions and leave comments. Thanks, and welcome to Stack Overflow. — Bill the Lizard
@Annie: I provided the correct XPath solution to your question. I don't understand SQL enough to know what you are asking with your updated question, and it seems that the problem is SQL-specific. As far as XQuery (not its old SQL-server dialect) is concerned, I have provided you with 2 answers that produce exactly the wanted data. — Dimitre Novatchev

Dimitre Novatchev Dimitre Novatchev · Accepted Answer · 2011-07-09T02:05:25

Use:

DriverDetails/DriverDetail[ID eq 1]/*[not(self::ID)]

or, if you consider this simpler:

DriverDetails/DriverDetail[ID eq 1]/(PRN | Name))

Here it is assumed that (for both XPath expressions) the expression is evaluated having as initial context node the parent of DriverDetails.

XQuery Filter like Where clause

2 Answers