Advice for the XML to db problem

Discussion in 'XML' started by Tim Jowers, Nov 21, 2003.

  1. Tim Jowers

    Tim Jowers Guest

    OK,

    I've hit the problem enough now to want to know what is the easy way
    :) Anybody know?

    I read in microsoft.public.sqlserver.xml group about doing this:
    1) Create an XML schema from the db schema using a tool like
    http://www.microsoft.com/downloads/...28-7071-4979-8a67-3cffcb0c2524&displaylang=en
    2) Map the schema using a tool like MapForce
    3) Use MSFT's version of BulkLoad to load up the incoming XML:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp

    Also, XMLSpy's MapForce says it does db-to-xml but does nowhere say
    it does xml-to-db. Anyone know if it does? Clearly the interesting
    cases is when the incoming XML in no way matches the schema and
    complex mapping including determingin whether to overwrite, add to, or
    ignore is needed (such as recording a jacket style for a person).

    Has anyone already addressed these issues or have comments? To date
    I have done systems using custom Java coding that does the mapping as
    well as ones that do some meta-coding by mapping matching db columns
    to matching XML Element names. As well, have used some XSLT to get
    into a "standard" XML and then loaded into the db from there.

    Ideally I think I should be able to open a tool and refer to the db
    and to the incoming schema. Then mapping should occur fairly
    dynamically (saw a presentation in school about some project doing
    this but did not write down the four tools quoted as already doing
    it), then I go and check over the mappings. Lastly, some external
    config is used each time to control whether to over-write, if/how to
    create new records, or to ignore on pre-existing. Does this exist?

    TIA,
    TimJowers
     
    Tim Jowers, Nov 21, 2003
    #1
    1. Advertising

  2. Tim Jowers

    GIMME Guest

    If you're using java you can combine JDOM and JDBC techology to
    create a method that will use JDBC meta data to fetch column
    names from the database and then build JDOM Elements.

    That kind of gives you what you want. The ability to make sql
    queries and get a List of JDOM Elements or a single JDOM Element.

    public static Element DoQueryAsElement( String sql , String label
    )
    throws ChainedException {
    BbConnection dbc = null;
    Element xmlData = null;
    try {
    dbc = new BbConnection();
    dbc.ProcessSQL(sql);
    xmlData = new Element( label + "s");
    while ( dbc.getResultSet().next() ) { // right indent to
    make easy to read
    int ccnt = dbc.getResultSet().getMetaData().getColumnCount();
    Element elm = new Element(label);
    for( int i = 1 ; i <= ccnt ; i ++ )
    {
    String colName =
    dbc.getResultSet().getMetaData().getColumnName(i);
    String content = dbc.SQLHelper(dbc.getResultSet().getString(i));
    elm.addContent(new
    Element(colName.toLowerCase()).addContent(content));
    }
    xmlData.addContent(elm);
    }
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoQueryAsElement failed
    : " + label + ":" + sql ) );
    }
    finally
    {
    try {
    dbc.closeResultSet();
    dbc.closeStatement();
    dbc.closeConnection();
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoQueryAsElement
    failed : " + sql ) );
    }
    }
    return xmlData;
    }

    public static Element DoQuerySelectRow( String sql , String label
    )
    throws ChainedException {
    BbConnection dbc = null;
    Element xmlData = null;
    try {
    dbc = new BbConnection();
    dbc.ProcessSQL(sql);
    xmlData = new Element( label );
    dbc.getResultSet().next();
    int ccnt = dbc.getResultSet().getMetaData().getColumnCount();
    for( int i = 1 ; i <= ccnt ; i ++ )
    {
    String colName =
    dbc.getResultSet().getMetaData().getColumnName(i);
    String content = dbc.SQLHelper(dbc.getResultSet().getString(i));
    xmlData.addContent(new
    Element(colName.toLowerCase()).addContent(content));
    }
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoQuerySingleRow failed : " + label
    + ":" + sql ) );
    }
    finally
    {
    try {
    dbc.closeResultSet();
    dbc.closeStatement();
    dbc.closeConnection();
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoQuerySingleRow
    failed : " + sql ) );
    }
    }
    return xmlData;
    }
    public static ArrayList DoQueryAsStringList( String sql )
    throws ChainedException {
    BbConnection dbc = null;
    ArrayList v = new ArrayList();
    try {
    dbc = new BbConnection();
    dbc.ProcessSQL(sql);
    while ( dbc.getResultSet().next() ) {
    v.add(dbc.getResultSet().getString(1));
    }
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoArrayList failed : "
    + sql ) );
    }
    finally
    {
    try {
    dbc.closeResultSet();
    dbc.closeStatement();
    dbc.closeConnection();
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoArrayList failed
    : " + sql ) );
    }
    }
    return v;
    }

    public static List DoQueryAsElementList( String sql , String label
    )
    throws ChainedException {
    BbConnection dbc = null;
    ArrayList v = new ArrayList();
    try {
    dbc = new BbConnection();
    dbc.ProcessSQL(sql);
    while ( dbc.getResultSet().next() ) {
    // See note 1 below
    int ccnt = dbc.getResultSet().getMetaData().getColumnCount();
    Element elm = new Element(label);
    for( int i = 1 ; i <= ccnt ; i ++ )
    {
    String colName =
    dbc.getResultSet().getMetaData().getColumnName(i);
    String content = dbc.SQLHelper(dbc.getResultSet().getString(i));
    elm.addContent(new
    Element(colName.toLowerCase()).addContent(content));
    }
    v.add(elm);
    }
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoQueryAsElementList failed :(" +
    label + "):" + sql ) );
    }
    finally
    {
    try {
    dbc.closeResultSet();
    dbc.closeStatement();
    dbc.closeConnection();
    } catch ( Exception ex ) {
    throw ( new ChainedException( ex, "DoQueryAsElementList failed :(" +
    label + "):" + sql ) );
    }
    }
    return v;
    }
     
    GIMME, Nov 24, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Asun Friere
    Replies:
    1
    Views:
    507
    Paul Boddie
    Aug 27, 2003
  2. Peter Hansen
    Replies:
    23
    Views:
    878
    Anton Vredegoor
    Sep 5, 2003
  3. Gerrit Holl
    Replies:
    16
    Views:
    625
    Tom Plunket
    Aug 29, 2003
  4. Asun Friere
    Replies:
    0
    Views:
    482
    Asun Friere
    Aug 28, 2003
  5. Michele Simionato
    Replies:
    2
    Views:
    391
    Jacek Generowicz
    Sep 1, 2003
Loading...

Share This Page