<customers>
<customer>
<name>xyz</name>
<address>1, Sacramento st</address>
<customer>
<customers>
I
want to map to the customer name to the customer(id, name, addr_id).
But the address to go custaddr table which has custaddr(id, address)
and the custaddr(id) goes to customer(addr_id ). I would like some
pointers to write a schema mapping for doing this.
I can change the xml format but I can't change the database design. Is it possible to do bulkload of this using XML Bulk load?
Thanks
vln
Hi ...
The link to this thread might help ...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174122&SiteID=1
Below is the Bulk Load implementation in C++.
Thanks,
Chris
Hi ...
I posted a question about Bulk Loading Xml data using C++ and the
SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Component. Below is the C++ code
that will do this. I have also included the xml, xsd and table
definition.
void CTestMeteorlogixApp::OnTestMsXmlBulkLoadRwis()
/* ///////////////////////////////////////////////////////////////////////
Method: CTestMeteorlogixApp::OnTestMsXmlBulkLoadRwis()
Description:
Microsoft XML Core Services (MSXML) offers several programmatic
extensions for writing XML applications. We will play with some
of these COM methods here.
Parameters: None
Return: None
Note:
Some of the code in this mehtod was derived from the following
discussion on xml:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp
Notes: ISQLXMLBulkLoad
We had to do a little work to get the ISQLXMLBulkLoad object in here.
First we installed the XMLSQL Component onto the computer. After this
we needed to find the dll that we are going to import. You will
find the import (#import <xblkld3.dll>) in stdafx.h.
We then added the code below thinking that we could create the
object this way. This did not work. We will talk about this
alitttle more later.
ISQLXMLBulkLoad pISQLXMLBulkLoad = NULL;
pXMLDoc.CreateInstance("SQLXMLBulkLoad.SQLXMLBulkload.3.0");
Using the object viewer in Visual Studio [Tools]+[OLE/COM Obejct Viewer]
we were able to find the SQLXMLBulkLoad Class. From this we were able
to find the path where the dll is located an then look at the
IDL by viewing the TypeLib of the Class [File]+[View TypeLib...].
Looking at the IDL I decided that I would implement the object
as I would from an COM class using the progID[] and CoCreateInstance().
So in the end that is what we have done and the code is below. We
are still testing but it all is working. Note that we also looked
at the readme.txt from from the SQLXML install and then have
noted that they copyed the following files and I know that we would be
needed these to implment the COM interface.
// You will need these.
// C:\Program Files\SQLXML 3.0\include:
Xmlblkld.h
Xmlblkld_i.c
/////////////////////////////////////////////////////////////////////// */
{
int nDataCount = 0;
int nCount = 0;
long nIndex = 0;
long nIndex2 = 0;
CString csErrorMessage;
CString csLogMessage;
CString csMethodName;
CString csMessage;
CString csXmlSchemaFile("C:\\Macgowan\\Project\\TestMeteorlogix\\Data\\rwis_oh.xsd");
CString csXmlDataFile("C:\\Macgowan\\Project\\TestMeteorlogix\\Data\\rwis_oh.xml");
CString
csXmlErrorLogFile("C:\\Macgowan\\Project\\TestMeteorlogix\\Data\\rwis_oh.err");
CString csTableName1("Alphanumericdata.dbo.MacgowanTestRWISRawAtmospheric");
CString csTableName2("Alphanumericdata.dbo.MacgowanTestRWISRawSurface");
CString csProvider("sqloledb");
CString csDataSource("SQLDEV");
CString csDatabase("Alphanumericdata");
CString csUserId("cmacgowan");
CString csPassword("7498757");
_bstr_t bstrXmlData;
_bstr_t bstrConnect;
_bstr_t bstrXmlSchemaFile;
_bstr_t bstrXmlDataFile;
_bstr_t bstrXmlErrorLogFile;
_bstr_t bstrSql;
variant_t vResult;
variant_t vXmlDataFile;
HRESULT hResult;
CString csXmlData;
CString csTemp;
CString csTemp2;
CString csResult;
CString csConnect;
CString csSql;
// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();
CFrameWnd* pChild = pMainFrame->GetActiveFrame();
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();
pView->WriteLog("Start OnTestMsXmlBulkLoadRwis().");
try
{
CoInitialize(NULL);
// Tell the user what is going on ...
pView->WriteLog("Bulk
load xml using the SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object");
csLogMessage.Format("Schema file: %s", csXmlSchemaFile);
pView->WriteLog(csLogMessage);
csLogMessage.Format("Data file: %s", csXmlDataFile);
pView->WriteLog(csLogMessage);
csLogMessage.Format("Error log file: %s", csXmlErrorLogFile);
pView->WriteLog(csLogMessage);
// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));
// Set the connection string
csConnect.Format("provider=%s;data Source=%s;database=%s;uid=%s;pwd=%s",
csProvider,
csDataSource,
csDatabase,
csUserId,
csPassword);
bstrConnect = csConnect.AllocSysString();
// Open the ado connection
pConnection->Open(bstrConnect,"","",adConnectUnspecified);
// Convert filenames from CString to varients
bstrXmlSchemaFile = csXmlSchemaFile.AllocSysString();
bstrXmlDataFile = csXmlDataFile.AllocSysString();
vXmlDataFile.SetString(LPCTSTR(csXmlDataFile));
bstrXmlErrorLogFile = csXmlErrorLogFile.AllocSysString();
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
// Now make that object.
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL,
IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_BulkLoad((bool)TRUE);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
if (SUCCEEDED(hResult))
{
pView->WriteLog("pISQLXMLBulkLoad->Execute() was successful.");
}
else
{
pView->WriteLog("Error: pISQLXMLBulkLoad->Execute()
failed.");
}
}
else
{
AfxMessageBox("Error: We could not find the ProgID!");
}
}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
pView->WriteLog("Error processing TestDatabase().");
}
catch(...)
{
pView->WriteLog("Error processing TestDatabase().");
}
pView->WriteLog("End OnTestMsXmlBulkLoadRwis().");
CoUninitialize();
return;
}
///////////////////////////////////////////////////////////////////////
// xsd schema
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>
<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>
<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
///////////////////////////////////////////////////////////////////////
// xml data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0"
name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM"
airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11"
windspeedgust="19" winddirectionavg="265" winddirectiongust="295"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="1900" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="450" waterlevel="0">
<traffic
datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82"
volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-60" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>
///////////////////////////////////////////////////////////////////////
// table definition
CREATE TABLE [MacgowanTestRWISRawAtmospheric] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[DataSourceId] [char] (4)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),
[ProductInstanceId] [char]
(38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT
('5abbbc86-fb2c-4703-9589-b55f763ee150'),
[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObsDateTime] [datetime] NULL ,
[InsertDateTime] [datetime]
NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime]
DEFAULT (getdate()),
[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED
(
[RecordId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO
