sql and xml: export data from a table to xml file

Question

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>

Darrel Miller · Accepted Answer · 2009-06-11 00:50:31Z

2

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')

answered Jun 11, 2009 at 0:50

Darrel Miller

143k32 gold badges202 silver badges246 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lambacck Over a year ago

Will the inner select work without having AS [node()]? When I was doing this a couple of days ago, I thought I found that it was necessary.

Darrel Miller Over a year ago

I just tried it on my SQL 2008 instance and it worked without.

user79127 Over a year ago

Darrel, According to your script I got <Contracts> <Contract contractid="123" contractname="John" contractType="Electrical"/> </Contracts> :-(

lambacck · Accepted Answer · 2009-06-11 01:11:06Z

1

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
    )
 )

edited Jun 11, 2009 at 1:11

answered Jun 11, 2009 at 0:47

lambacck

9,9463 gold badges37 silver badges46 bronze badges

1 Comment

user79127 Over a year ago

One more question please. Is there anyway I can save the result to the file? Thanks.

Joel Coehoorn · Accepted Answer · 2009-06-11 00:21:04Z

0

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.

answered Jun 11, 2009 at 0:21

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

sql and xml: export data from a table to xml file

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related