CODEWITHSUNDEEP
sqlsql-serverxmlopenxmlsql-server-openxml
user2994641
user2994641

Reputation: 125

OpemXML - Read last node in SQL

I have a XML like this

<Cat>
    <Inner>
        <PATCat>
            <Pat>SUR</Pat>
            <EfDa>20170411093000</EfDa>
        </PATCat>
        <PATCat>
            <Pat>MH</Pat>
            <EfDa>20170411094100</EfDa>
        </PATCat>
        <PATCat>
            <Pat>NRO</Pat>
            <EfDa>20170411095300</EfDa>
        </PATCat>
        <PATCat>
            <Pat>DAY</Pat>
            <EfDa>20170411110900</EfDa>
        </PATCat>
    </Inner>
</Cat>

and I am using the Query to read the nodes Pat and EfDa

SELECT      @PATCat_Pat = Pat,      
                    @PATCat_EfDa = EfDa,        

        FROM    OPENXML(@idoc, '/Cat/Inner', 2)
        WITH    (
            FiCl VARCHAR(20) 'PATCat/Pat',
            EfDa VARCHAR(20) 'PATCat/EfDa', 
            )

The result is @PATCat_Pat = SUR and @PATCat_EfDa = 20170411093000, Whereas I want to read the last node which is "DAY" and "20170411110900"

How can I achieve this? any help would be appreciated

Thanks

Upvotes: 0

Views: 1287

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

FROM OPENXML with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more (rare exceptions exist). Rather use the appropriate methods the XML data type provides.

From a comment I get, that your function gets the handle, so you'll have to stick with this...

In your question you write, that you want to read the last node which is "DAY" and "20170411110900".
What is your criterion? last or the one with <Pat>="DAY" or - if there might be more of the same - the last of all <PATCat>, which has <Pat>="DAY"? Are the elements always in the same order? Is the last <PATCat> always the one with <PAT>="DAY"?

You have got the solution with last() already. It will find the last <PATCat> no matter what's inside:

'/Cat/Inner/PATCat[last()]'

Looking for the one with "DAY" would be this

'/Cat/Inner/PATCat[(PAT/text())[1]="DAY"][1]'

If there might be more with "DAY" you could replace the last [1] with [last()]

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

last() can be used with OPENXML as well.

SELECT Pat,      
       EfDa        
FROM OPENXML(@idoc, '/Cat/Inner/PATCat[last()]', 2)
WITH (
     Pat VARCHAR(20) 'Pat',
     EfDa VARCHAR(20) 'EfDa'
     );

Upvotes: 0

TriV
TriV

Reputation: 5148

You should use value() and last() for xml type instead of OPENXML

DECLARE @xml XML = N'<Cat>
   <Inner>
      <PATCat>
         <Pat>SUR</Pat>
         <EfDa>20170411093000</EfDa>
      </PATCat>
      <PATCat>
         <Pat>MH</Pat>
         <EfDa>20170411094100</EfDa>
      </PATCat>
      <PATCat>
         <Pat>NRO</Pat>
         <EfDa>20170411095300</EfDa>
      </PATCat>
      <PATCat>
         <Pat>DAY</Pat>
         <EfDa>20170411110900</EfDa>
      </PATCat>
   </Inner>
</Cat>'

SELECT @xml.value('(Cat/Inner/PATCat[last()]/Pat)[1]', 'varchar(10)') AS PAT,
   @xml.value('(Cat/Inner/PATCat[last()]/EfDa)[1]', 'varchar(30)') AS EfDa

Return 

PAT     EfDa <br/>
DAY     20170411110900

Upvotes: 0

Related Questions