Advice for the XML to db problem

[Tim Jowers]

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/d...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/de...kload_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

[GIMME]

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;
}