Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: SQL For XML - Stop repeating elements

    Hi all,

    I'm hoping someone can assist me here. I know this is hashed but I'm only learning at the moment. I need to select the data in a table in an XML format. I'm getting there, but the first 2 values ('RECID','DBID') keep repeating each with each transaction.

    My code is stripped down for ease.

    Code:
    SELECT	'12025' as 'RECID',
    		'10007782' as 'DBID',
    		p.SURNAME as 'Person/Surname',
    		p.FORENAME as 'Person/Forename',
    
    FROM		Record_Person p, Record_Contract c
    WHERE	p.UKEY = c.UKEY
    
    For xml path (''), ROOT ('Institution')

    At the moment, the output format is ;

    .<Institution>
    . <RECID> 02012 </REDID>
    . <DBID> 32010 </DBID>
    . <PERSON>
    . <Surname> BLOGG </Surname>
    . <Forename> JOE </Forename>
    . </PERSON>
    . <RECID> 02012 </REDID>
    . <DBID> 32010 </DBID>
    . <PERSON>
    . <Surname> DOE </Surname>
    . <Forename> JANE </Forename>
    . </PERSON>
    .</Institution>

    I only need the RECID, and the DBID to appear once, directly under Institution, then all the person records, such as;

    <Institution>
    <RECID> 02012 </REDID>
    <DBID> 32010 </DBID>
    <PERSON> </PERSON>
    <PERSON> </PERSON>
    <PERSON> </PERSON>
    <PERSON> </PERSON>
    </Institution>


    Can someone explain what I'm doing wrong?

    Thank you.
    Last edited by christyxo; 04-12-13 at 10:04.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    SELECT
        '12025' as 'RECID',
        '10007782' as 'DBID',
        (SELECT
             p.SURNAME as 'Surname',
             p.FORENAME as 'Forename'
         FROM Record_Person p
         INNER JOIN Record_Contract c
             ON p.UKEY = c.UKEY
         FOR XML PATH('Person'), TYPE)
    For xml path (''), ROOT ('Institution')
    Hope this helps.

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Superb! Thank you imex, this solved my issue and helped me understand how to nest the paths (I tried this way but failed on my own!).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •