Solved

Need to generate row number using Xquery

Posted on 2010-08-15
2
1,132 Views
Last Modified: 2013-11-26
Hi,

I would like to generate a unique row number ( starting from 1 ) to the
result set generated from the query below.   I know that I could insert the
results into a table with an identity column, but I need to have
a unique row number produced without doing any table inserts.


BEGIN

DECLARE @TheXML XML

SELECT

      SUBSTRING(doc.col.value('first_name[1]', 'varchar(30)'),1,30) first_name,
      SUBSTRING(doc.col.value('surname[1]', 'varchar(35)') ,1,35) surname,
      SUBSTRING(doc.col.value('title[1]', 'varchar(4)') ,1,4) title,
      SUBSTRING(doc.col.value('gender[1]', 'varchar(6)') ,1,6) gender


FROM @thexml.nodes('/jobs/applicant') doc(col)

END

Thanks
0
Comment
Question by:cintra
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33439982
BEGIN
DECLARE @TheXML XML
set @TheXML = '<jobs>
<applicant><firstname>jim</firstname><surname>john</surname><title>mr</title><gender>m</gender></applicant>
<applicant><firstname>tim</firstname><surname>jack</surname><title>mr</title><gender>m</gender></applicant>
<applicant><firstname>tom</firstname><surname>jill</surname><title>mrs</title><gender>f</gender></applicant>
</jobs>'
SELECT
      Row_Number() over (order by doc.col.value('@position','int')),
      SUBSTRING(doc.col.value('first_name[1]', 'varchar(30)'),1,30) first_name,
      SUBSTRING(doc.col.value('surname[1]', 'varchar(35)') ,1,35) surname,
      SUBSTRING(doc.col.value('title[1]', 'varchar(4)') ,1,4) title,
      SUBSTRING(doc.col.value('gender[1]', 'varchar(6)') ,1,6) gender
FROM @thexml.nodes('/jobs/applicant') doc(col)
END
0
 

Author Closing Comment

by:cintra
ID: 33443381
Thnak you. That's exactly what I was looling for?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question