Click here to Skip to main content
14,451,574 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,
Assume I have table with xml datatype. Consider below value as data in xml.

<Books>
  <Book ID="BK1">
    <Pages>
      <Page ID="Home" />
      <Page ID="Index" />
      <Page ID="Introduction" />
    </Pages>
  </Book>
  <Book ID="BK2">
    <Pages>
      <Page ID="Home" />
      <Page ID="Introduction" />
    </Pages>
  </Book>
  <Book ID="BK3">
    <Pages>
      <Page ID="Home" />
      <Page ID="Index" />
      <Page ID="Introduction" />
    </Pages>
  </Book>
  <Book ID="BK4">
    <Pages>
      <Page ID="Home" />
      <Page ID="Introduction" />
    </Pages>
  </Book>
</Books>





I want list of book ID for which Page ID=Index is missing? (For ex, in Book ID=BK2 and ID=BK4, Page ID=Index is missing?

Output:
BookId
BK2
BK4

What I have tried:

SELECT Data.Col.value('(@ID)[1]','Varchar(100)') AS Books
FROM @x.nodes('/Books/Book/Pages/Page') AS Data(Col)
WHERE @x.exist('(/Books/Book/Pages/Page[@ID = sql:variable("@BookId")])') = 1
Posted
Updated 21-May-19 3:58am
v4

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You don't need to specify the full path in the exist test; you just want to test against the current <Book> node, rather than the whole document:
SELECT data.col.value('@ID', 'varchar(100)')
FROM @x.nodes('/Books/Book') As data(col)
WHERE data.col.exist('Pages/Page[@ID="Index"]') = 0;
   
v2
Comments
Dilip Mevada 22-May-19 7:59am
   
Thanks for solution. Its works perfectly.

Can we fetch data directly from SQL Table instead of Nodes? For example. Books table.
I have additional where clause with SQL Table. Please suggest.
Richard Deeming 22-May-19 8:32am
   
If the XML is stored in a column in your table, then you need to use CROSS APPLY to extract the nodes:
Specifying the nodes() method against a column of xml type | nodes() Method (xml Data Type) - SQL Server | Microsoft Docs[^]
SELECT data.col.value('@ID', 'varchar(100)')
FROM YourTableWithAnXmlColumn As T
CROSS APPLY T.YourXmlColumn.nodes('/Books/Book') As data(col)
WHERE T.SomeColumn = @SomeValue
And data.col.exist('Pages/Page[@ID="Index"]') = 0;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100