sql and xml

Question

Hi there,

I try to find out to export data from a table to xml file. Here is the situation

Table1
contractID     subcontractid     ContractName     ContractType
123                              John             Electrical
123            1                 John             Comercial
123            2                 John             Resident
134                              Jim              Plumping
134            1                 Jim              Comercial
134            2                 Jim              Resindent

I'd like to have an xml file output as following]

<contracts>
  <contract contractid = 123 contractname = john, contracttype = Electrical>
    <subcontract subcontractid = 1, contractname = John, contractType = Comercial />
    <subcontract subcontractid = 2, contractname = John, contractType = Resident />
  </contract>
  <contract contractid = 134 contractname = Jim, contracttype = Plumping>
    <subcontract subcontractid = 1, contractname = Jim, contractType = Comercial />
    <subcontract subcontractid = 2, contractname = Jim, contractType = Resident />
  </contract>
</contracts>

Thank for all helps.

Answer 1

The specific database flavor will make a huge difference in the options available to you.

SQL Server, for example, allows you to append a "FOR XML" clause to the end of a query, but oracle, mysql, and other db's will require different approaches.

Answer 2

For SQL Server 2005/2008 you would do something like:

SELECT 
    contractID as [@contractid], 
    ContractName as [@contractname], 
    ContractType As [@contracttype], 
    ( SELECT 
        subcontractid as [@subcontractid], 
        ContractName as [@contractname], 
        ContractType As [@contracttype] 
      FROM Table1 AS inner 
      WHERE outer.contractID=inner.contractID AND 
          subcontractid IS NOT NULL FOR XML PATH('subcontract'), TYPE ) as [node()] 
    WHERE subcontractid IS NULL
 FOR XML PATH('contract'), ROOT('contracts')

For Oracle: Something like:

SELECT XMLELEMENT('contracts', 
    (SELECT XMLELMENT('contract', 
        XMLAgg(
            XMLAttributes(
                contractID as 'contractid', 
                ContractName as 'contractname', 
                ContractType as 'contracttype'), 
           (SELECT XMLElement('subcontract', 
               XMLAgg(
                   XMLAttributes(
                       subcontractid as 'subcontractid', 
                       ContractName as 'contractname', 
                       ContractType as 'contracttype'
                   )
               ) 
            FROM Table1 AS inner 
            WHERE inner.contractID=outer.contractID AND subcontractid IS NOT NULL 
           )
         )   
      )
     FROM Table1 AS outer WHERE subcontractid IS NULL
    )
 )

Answer 3

Sheesh, beaten by 50 secs! I'll upvote you lambacck for speed :-)

SELECT co.contractid AS '@contractid',
    co.contractname AS '@contractname',
        co.contracttype AS '@contractType'
        (SELECT sc.subcontractid AS '@subcontractid', 
     sc.contractname AS '@contractname', 
     sc.contractType AS '@contractType'
         FROM contract sc
     WHERE subcontractid IS NOT NULL AND sc.contractid = co.contractid
     FOR XML PATH('subcontract'),Type ) 
FROM Contract co
WHERE subcontractid IS NULL
FOR XML PATH('Contract'), ROOT('Contracts')

Answer 4

I love all solutions. Thanks all yours helps.