0

I have a XML Document file. The part of the file looks like this:

-<attr>  
     <attrlabl>COUNTY</attrlabl>  
     <attrdef>County abbreviation</attrdef>  
     <attrtype>Text</attrtype>  
     <attwidth>1</attwidth>  
     <atnumdec>0</atnumdec>  
    -<attrdomv>  
        -<edom>  
            <edomv>C</edomv>  
            <edomvd>Clackamas County</edomvd>  
            <edomvds/>  
         </edom>  
        -<edom>  
            <edomv>M</edomv>  
            <edomvd>Multnomah County</edomvd>  
            <edomvds/>  
         </edom>  
        -<edom>  
            <edomv>W</edomv>  
            <edomvd>Washington County</edomvd>  
            <edomvds/>  
         </edom>  
     </attrdomv>  
 </attr>

From this XML file, I want to create a PostgreSQL table with columns of attrlabl, attrdef, attrtype, and attrdomv. I appreciate your suggestions!

POTENZA
  • 1,377
  • 3
  • 17
  • 20
  • attrlabl has one item, and attrdomv has children and sub-children. This does not translate into any table format. If you want to force it, you'll have to utilize an XML parser and roll your own translation program to generate SQL from it. – Eric Leschinski Nov 26 '12 at 20:53
  • 1
    @EricLeschinski: Actually, it can be done with PostgreSQL tools, but it's complex and I don't have the time now. I posted a related answer [here](http://stackoverflow.com/a/7628453/939860) some time ago. – Erwin Brandstetter Nov 26 '12 at 21:13

1 Answers1

0

While Erwin is right that this can be done with PostgreSQL tools, I would suggest still going the custom translation yourself as there are a few reasons here.

The first is determining appropriate XML to PostgreSQL type conversions. You probably want to choose these yourself. But this example highlights a very different problem, what to do with nested data structures. You could, for example, store XML fragments. You could store text, json, or the like. You could create other tables and fkey in.

In general I have almost always found the best approach is to simply manually create the tables. This substitutes human judgement for automated mappings and allows you to create better matches than a computer will.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182