August 19, 2008 at 11:23 am
I am trying to extract all Lists as well as the JobID, from the below schema.
Here is the desired output:
JobId ListId
123843 906364
123843 906365
123843 906366
123843 906367
123845 1684583
XML FILE (contents in either XML table column, or @xml XML variable). I had to omit the open brackets on this post.
jobs>
job>
jobID>123843 /jobID>
jobDate>8/1/2008 10:16:00 AM /jobDate>
lists>
listID>906364 /listID>
listID>906365 /listID>
listID>906366 /listID>
listID>906367 /listID>
/lists>
/job>
job>
jobID>123845 /jobID>
jobDate>8/1/2008 12:01:00 PM /jobDate>
lists>
listID>1684583 /listID>
/lists>
/job>
/jobs>
Here is an example of what I have tried and failed with. The problem here is getting the jobid. It at least returns all ListIds.
SELECT nref.value('..job/jobID[1]', 'varchar(20)') jobID,
nref.value('listID[1]', 'varchar(20)') listID
FROM @xml.nodes('//lists') AS R(nref)
Any help is greatly appreciated!!!
August 19, 2008 at 11:28 am
I also wanted to add that I have tried the following, which is my preferred method if it could work.
EXEC sp_xml_preparedocument @hdoc OUT,
@xml
SELECT jobID,
listID
FROM OPENXML (@hdoc, '/xml/jobs/job/lists',2)
WITH (jobID int '../jobID',
listID int 'listID')
Again, the goal is to get all ListIds along with the JobID, which is the many-to-one relationship. That is, many ListIds to one JobId. The problem with the above query is that is only returns one ListId even if there are many for the particular job.
Thanks again!
August 21, 2008 at 5:15 am
The thing is that you need to go to the listID node in the xml hierarchy and access jobID from there.
SELECT R.nref.value('../../jobID[1]', 'varchar(20)') [jobID], R.nref.value('.','varchar(20)') [listID]
FROM @xml.nodes('/jobs/job/lists/listID') AS R(nref)
Welcome!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply