CODEWITHSUNDEEP
sqlsql-servert-sql
David
David

Reputation: 159

SQL Server XML query with namespaces in the where exist

I am trying to query xml in SQL Server using the exist() method in the where clause. The select portion of the query gets the first occurrence of "SourceIndex" but the where clause has no affect at all. I want to get the first occurrence of "SourceIndex" where the "Source" is the given OID. I also looked at the nodes() method but couldn't make that work with a where clause either.

Here is my query

 Create table #temp ( identXml xml)
Select 
        #temp.identXml.value('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:SourceIndex)[1]','varchar(100)') as Ident
        ,#temp.identXml.value('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:Source)[1]','varchar(100)') as SourceOID
        from #temp
         WHERE #temp.identXml.exist('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:Source)[text() = "00.000.000.00.1.3.43.1.1.8.10"]')=1

Here is a sample of the xml

    Declare @xml xml

Set @xml= '<PersonIdentity xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <CurrentID>87bf-4fcb-8dd9-4e2c43ec73ba</CurrentID>
  <MasterIndexes>
    <PersonIndex>
      <CreationDate>2017-04-27 12:00:00 A.M.</CreationDate>
      <Source>3.57.1.3.43.1.1.8.10</Source>
      <SourceIndex>Foo1737</SourceIndex>
      <SourceType>SYS</SourceType>

    </PersonIndex>
    <PersonIndex>
      <CreationDate>2017-04-25 12:00:00 A.M.</CreationDate>
      <Source>3.57.1.3.43.1.4.1.8.6</Source>
      <SourceIndex>Foo002194</SourceIndex>
      <SourceType>Foo2</SourceType>



    </PersonIndex>
    <PersonIndex>
      <CreationDate>2017-04-25 12:00:00 A.M.</CreationDate>
      <Source>3.57.1.3.43.102.1.8.1</Source>
      <SourceIndex>f00189854</SourceIndex>
      <SourceType>SYS</SourceType>

    </PersonIndex>
    <PersonIndex>
      <CreationDate>2017-07-05 12:00:00 A.M.</CreationDate>
      <Source>3.57.1.3.43.2.1.8.6</Source>
      <SourceIndex>foo379</SourceIndex>
      <SourceType>SYS</SourceType>

    </PersonIndex>
  </MasterIndexes>
</PersonIdentity>'


DECLARE @exist BIT;

SET @exist = @xml.exist('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:Source)[text() = "3.57.1.3.43.1.1.8.10"]');
SELECT @exist;

Update

Based on the feed back below I came up with this SQL which seems to work. I tried to post the code in the comments below but couldn't figure out the formatting.

   Select 
    t.c.query('./*:SourceIndex').value('.', 'varchar(50)') as Ident
    From @xml.nodes('/*:PersonIdentity/*:MasterIndexes/*:PersonIndex') as t(c)
    Where  t.c.exist('./Source[text() = "3.57.1.3.43.1.1.8.10"]') =1;

Upvotes: 0

Views: 286

Answers (1)

BobNoobGuy
BobNoobGuy

Reputation: 1645

Instead of #temp.identXml.exist you may want to use #temp.identXml.query. You can read more about this in here SQL Server XML exist()

I believe you can also use it like this

Select 
#temp.identXml.value('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:SourceIndex)[1]','varchar(100)') as Ident
,#temp.identXml.value('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:Source)[1]','varchar(100)') as SourceOID
from #temp
WHERE #temp.identXml.query('(/*:PersonIdentity/*:MasterIndexes/*:PersonIndex/*:Source)[1]').value('.', 'varchar(100)') = 'Something'

Upvotes: 2

Related Questions