XML - XQUERY

  • 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!!!

  • 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!

  • 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