Loading XML straight into the DB

Hi
I have been tasked with loading some relational data into the database.  I want to utilise the XML-based power of Ado.net.
I can write out data expressed in two tables to XML easily enough when there is a PK-FK relationship between the two source tables.

The XML could be an object serialised to file, or it could be a dataset from someone else's DB.

The tables look like this:

Parent
  Parent_id (PK, uniqueidentifier)
  Parent_A
  Parent_B
  Parent_Z (has a default value)

Child:
   Child_ID  (PK, uniqueidentifier)
   Child_Parent_Id (FK, uniqueidentifier)
   Child_X
   Child_Y
   Child_Z (has a defualt value)

The select staement to use is of course
       select * from Parent inner join child on parent.parent_id = child.child_parent_id
 

But what about loading data from XML into the DB when the XML source data is nested but there are no IDs?

e.g.
<dataset>
   <parent>
      <A>   I am A </A>
      <B>   I am B </B>
      <childrow>  
            <X>I am x </Y>
            <Y>I am y </Y>
      </childrow>
      <childrow>  
            <X>I am another x </Y>
            <Y>I am another y  </Y>
      </childrow>
   <parent>
</dataset>

 

There can be 1+ children per parent.  


Please advise how to load this XML into the relational database, taking care to note that:
   a) source tag names are not necissarily the same as the target column names
   b) source tag names for child and parent are not necessarily the same as the target table names in the DB
   c) IDs used for PK and FK in the resulting tables are uniqueidentifiers.
  d) there are additional columns in the tables which do not have nodes in the XML (but these columns have default values)



Many thanks
Daniel


ps.  SQL 2000, VS2003, .net 1.1, windows server 2003.
jaswwAsked:
Who is Participating?
 
DarthModConnect With a Mentor Commented:
Closed, 300 points refunded.
DarthMod
Community Support Moderator
0
 
jaswwAuthor Commented:
Ooooh sweet

Let me give it a shot.

Thanks
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jaswwAuthor Commented:
Oh, do you have an example of using ADO.net rather than ado6 or vbs?

THanks.
0
 
YZlatCommented:
you can load XML into a DataSet and then update the database
0
 
YZlatCommented:
'load xml into a dataset
Dim dS As New DataSet
Dim fs As FileStream
fs = New FileStream("C:\someXMLfile.xml, FileMode.Open)
dS.ReadXml(fs)

then do the following:

UpdateDatabase("your db connection string here", "your select statement here", ds.Tables(0))

You will also need functions below:

Function GetConnection(ByVal strConn As String) As SqlConnection
dim conn As SqlConnection
        Try
            conn = New SqlConnection(strConn)
            conn.Open()
        Catch ex As SqlException
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        End Try
        GetConnection = conn
    End Function


 Sub UpdateDatabase(ByVal strConn As String, ByVal query As String, ByVal dt As System.Data.DataTable)
dim conn As SqlConnection
        Dim ds As New DataSet
        Dim dsTemp As New DataSet
        conn = GetConnection(strConn)
        Dim da As SqlDataAdapter = New SqlDataAdapter(query, conn)
        Dim cmdBuilder As SqlCommandBuilder
        Try
            ''fill dataset
            da.Fill(ds)

            ''add datatable to a temp dataset
            dsTemp.Tables.Add(dt)
            ''merge two datasets together
            ds.Merge(dsTemp)
            ''setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database
            cmdBuilder = New SqlCommandBuilder(da)

            da.Update(ds)
        Catch ex As Exception

            Console.Write("ERROR: " & ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
    End Sub
0
 
jaswwAuthor Commented:
In the ado.net example, I see exactly what you are doing, but how do we map the schema of the source XML file to the DB's schema, including the relationships?
0
 
jaswwAuthor Commented:
OK, I can see several easy ways of loading a very flat structure such as a list of customers, a list of regios, etc.
Basically treating myXMl file as if it were a dataset created from a SIMPLE statement such as
"select customer name, customer id from customers"

Fine.


But my question has two tables - see above.
Some of the real XML is shown below.


Please advise how to load this XML into the DB and update parent and child tables and have the PK and FK between them.
The roto node is Schedule, which has many ScheduleDetails nodes. Each of these may have 1+ RoutingDetails nodes.
Many thanks






<Schedule>
    <ScheduleDetails>
        <VesselVoyageID>11499412</VesselVoyageID>
        <VesselName>KOOKABURRA 1</VesselName>
        <IMONumber>9319571</IMONumber>
        <Voyage>611</Voyage>
        <CarrierSCAC>USLB</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>AUADL</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>2</Transportmode>
            <TransportName>RAIL</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-10-23</ETD>
            <Destination>USLAX</Destination>
            <ETA>2006-10-31</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>3</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>KOOKABURRA 1</TransportName>
            <IMONumber>9319571</IMONumber>
            <Origin>USLAX</Origin>
            <ETD>2006-11-02</ETD>
            <Destination>AUMEL</Destination>
            <ETA>2006-11-22</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>5</StageQualifier>
            <Transportmode>5</Transportmode>
            <TransportName>TBN</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>AUMEL</Origin>
            <ETD>2006-11-22</ETD>
            <Destination>AUADL</Destination>
            <ETA>2006-11-25</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>AUADL</Origin>
            <ETD>2006-11-25</ETD>
            <Destination>AUADL</Destination>
            <ETA>2006-11-25</ETA>
        </RoutingDetails>
    </ScheduleDetails>
    <ScheduleDetails>
        <VesselVoyageID>11499786</VesselVoyageID>
        <VesselName>MAERSK DALE</VesselName>
        <IMONumber>9232577</IMONumber>
        <Voyage>608</Voyage>
        <CarrierSCAC>HLCU</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>NZAKL</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-10-23</ETD>
            <Destination>USSAV</Destination>
            <ETA>2006-10-28</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>2</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>MAERSK DALE</TransportName>
            <IMONumber>9232577</IMONumber>
            <Origin>USSAV</Origin>
            <ETD>2006-10-28</ETD>
            <Destination>NZAKL</Destination>
            <ETA>2006-11-17</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>NZAKL</Origin>
            <ETD>2006-11-17</ETD>
            <Destination>NZAKL</Destination>
            <ETA>2006-11-17</ETA>
        </RoutingDetails>
    </ScheduleDetails>
    <ScheduleDetails>
        <VesselVoyageID>11542719</VesselVoyageID>
        <VesselName>CAP MATATULA</VesselName>
        <IMONumber>9127526</IMONumber>
        <Voyage>13</Voyage>
        <CarrierSCAC>SUDU</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>WSAPW</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>2</Transportmode>
            <TransportName>RAIL</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-10-06</ETD>
            <Destination>USLAX</Destination>
            <ETA>2006-10-16</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>2</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>CAP MATATULA</TransportName>
            <IMONumber>9127526</IMONumber>
            <Origin>USLAX</Origin>
            <ETD>2006-10-29</ETD>
            <Destination>WSAPW</Destination>
            <ETA>2006-11-14</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>WSAPW</Origin>
            <ETD>2006-11-14</ETD>
            <Destination>WSAPW</Destination>
            <ETA>2006-11-14</ETA>
        </RoutingDetails>
    </ScheduleDetails>
    <ScheduleDetails>
        <VesselVoyageID>11542720</VesselVoyageID>
        <VesselName>POLYNESIA</VesselName>
        <IMONumber>9127019</IMONumber>
        <Voyage>363</Voyage>
        <CarrierSCAC>PLLU</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>WSAPW</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>2</Transportmode>
            <TransportName>RAIL</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-10-20</ETD>
            <Destination>USLAX</Destination>
            <ETA>2006-10-30</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>2</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>POLYNESIA</TransportName>
            <IMONumber>9127019</IMONumber>
            <Origin>USLAX</Origin>
            <ETD>2006-11-07</ETD>
            <Destination>WSAPW</Destination>
            <ETA>2006-11-21</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>WSAPW</Origin>
            <ETD>2006-11-21</ETD>
            <Destination>WSAPW</Destination>
            <ETA>2006-11-21</ETA>
        </RoutingDetails>
    </ScheduleDetails>
    <ScheduleDetails>
        <VesselVoyageID>11542721</VesselVoyageID>
        <VesselName>CAP MATATULA</VesselName>
        <IMONumber>9127526</IMONumber>
        <Voyage>14</Voyage>
        <CarrierSCAC>SUDU</CarrierSCAC>
        <SCAC>NAQA</SCAC>
        <AmsFlag>N</AmsFlag>
        <CFSOrigin>USATL</CFSOrigin>
        <CFSDestination>WSAPW</CFSDestination>
        <RoutingDetails>
            <StageQualifier>1</StageQualifier>
            <Transportmode>2</Transportmode>
            <TransportName>RAIL</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>USATL</Origin>
            <ETD>2006-11-06</ETD>
            <Destination>USLAX</Destination>
            <ETA>2006-11-17</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>2</StageQualifier>
            <Transportmode>4</Transportmode>
            <TransportName>CAP MATATULA</TransportName>
            <IMONumber>9127526</IMONumber>
            <Origin>USLAX</Origin>
            <ETD>2006-11-26</ETD>
            <Destination>WSAPW</Destination>
            <ETA>2006-12-10</ETA>
        </RoutingDetails>
        <RoutingDetails>
            <StageQualifier>6</StageQualifier>
            <Transportmode>1</Transportmode>
            <TransportName>TRUCK</TransportName>
            <IMONumber>
            </IMONumber>
            <Origin>WSAPW</Origin>
            <ETD>2006-12-10</ETD>
            <Destination>WSAPW</Destination>
            <ETA>2006-12-10</ETA>
        </RoutingDetails>
    </ScheduleDetails>
</Schedule>
0
 
jaswwAuthor Commented:
These are the tables I am trying to populate:


create table
tbl_SailingSchedule_RoutingDetials
(
      RoutingDetials_id uniqueidentifier not null primary key --PK,
      RoutingDetials_SailingId uniqueidentifier not null, --fk to tbl_SailingSchedule.SailingSchedule_id
      RoutingDetials_StageQualifier int,
      RoutingDetials_Transportmode int ,
      RoutingDetials_TransportName nvarchar(255),
      RoutingDetials_IMONumber int,
      RoutingDetials_Origin char(5),
      RoutingDetials_Destination char(5),
      RoutingDetials_ETD datetime,
RoutingDetials_ETA datetime
)




create table
tbl_SailingSchedule
(
      SailingSchedule_id uniqueidentifier not null primary key --PK,
      SailingSchedule_VesselVoyageID int,
      SailingSchedule_VesselName nvarchar(255),
      SailingSchedule_IMONumber int,
      SailingSchedule_Voyage nvarchar(10),
      SailingSchedule_CarrierSCAC char(4),
      SailingSchedule_SCAC char(4),
      SailingSchedule_AmsFlag char(1),
      SailingSchedule_CFSOrigin char(5),
      SailingSchedule_CFSDestination char(5)
)
0
 
jaswwAuthor Commented:
I can see how to do bulk-loading for a simple data (thanks for examples) set but my original question regards two related tables.

Can someone show me how to load the two related tables from the one XML file?


Thanks

0
 
YZlatCommented:
I posted the code for you
0
 
jaswwAuthor Commented:
Does that put values into the two PK and the one FK columns too?
ie  RoutingDetials_id  (PK)
   RoutingDetials_SailingId  (FK)
and   SailingSchedule_id   (PK)
0
 
YZlatCommented:
here is xsd schema for your xml:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="Schedule" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="Schedule" msdata:IsDataSet="true">
    <xs:complexType>
      <xs:choice maxOccurs="unbounded">
        <xs:element name="ScheduleDetails">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="VesselVoyageID" type="xs:string" minOccurs="0" />
              <xs:element name="VesselName" type="xs:string" minOccurs="0" />
              <xs:element name="IMONumber" type="xs:string" minOccurs="0" />
              <xs:element name="Voyage" type="xs:string" minOccurs="0" />
              <xs:element name="CarrierSCAC" type="xs:string" minOccurs="0" />
              <xs:element name="SCAC" type="xs:string" minOccurs="0" />
              <xs:element name="AmsFlag" type="xs:string" minOccurs="0" />
              <xs:element name="CFSOrigin" type="xs:string" minOccurs="0" />
              <xs:element name="CFSDestination" type="xs:string" minOccurs="0" />
              <xs:element name="RoutingDetails" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="StageQualifier" type="xs:string" minOccurs="0" />
                    <xs:element name="Transportmode" type="xs:string" minOccurs="0" />
                    <xs:element name="TransportName" type="xs:string" minOccurs="0" />
                    <xs:element name="IMONumber" type="xs:string" minOccurs="0" />
                    <xs:element name="Origin" type="xs:string" minOccurs="0" />
                    <xs:element name="ETD" type="xs:string" minOccurs="0" />
                    <xs:element name="Destination" type="xs:string" minOccurs="0" />
                    <xs:element name="ETA" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>
0
 
YZlatCommented:
try using something like this:

Private Sub InsertData()

        Dim conn As New SqlConnection("SERVER=.;UID=sa;PWD=;DATABASE=MyTest;")
        Dim da As SqlDataAdapter
        Dim dr As DataRow 'Dataset row
        Dim drow As DataRow 'SQL Server table row
        Dim ds As New DataSet
        Dim dst As New DataSet("TableName")
        Dim ObjCmdBuilder As SqlCommandBuilder

        Try
            ds.ReadXml("C:\myXML.xml")
            conn.Open()
            da = New SqlDataAdapter("SELECT * FROM Table1 WHERE ...", conn)
            da.Fill(dst, "TableName")

            For Each dr In ds.Tables(0).Rows

                With dst.Tables(0)
                    drow = .NewRow()
                    drow(0) = dr(0)
                    drow(1) = dr(1)
                    .Rows.Add(drow)
                End With

                ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
                da.Update(dst, "TableName")

            Next
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            If Not conn Is Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
            End If
        End Try
    End Sub
0
 
jaswwAuthor Commented:
I have tried this and it's not working.

One, you seem to only write the first two values (you only copy elements (0) and (1)) from the XML row into the database row.
This fails because dr(0) and dr(1) are, respectively
VesselVoyageID and VesselName
but drow(0) and drow(1) are, respectively,  SailingSchedule_id and SailingSchedule_VesselVoyageID.

If all you are doing is mapping source rows to target rows, and we have to say something like
 drow(0) = System.Guid.NewGuid()
 drow(1) = dr(0)
 drow(2) = dr(1)
 drow(3) = dr(2)
...
etc
 then this is NOT what I am looking for.  This is not "loading XML straight into the database".
 


Two, this will STILL not ties the child rows to the parent.



Let me be very clear with what I want.
I want to load the source XML file into the DB in one fell swoop and end up with child rows tied to their parents by foreign key.
The examples on the MS site were almost thee, but they showed only how to load a simple flat structure.  
0
 
jaswwAuthor Commented:
OK, I think I almost have it, using SQLXML.

I just need help with one error mesage please.  I have a relationship in the DB between the parent and the child table, and in the xml schema I describe this relationship, but the insert fails because my source xml data file (obviously) contains no data for the FK column in the child row.

Please advise.

CODE:


try
                  {
                        SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
                        objBL.ConnectionString = "Provider=sqloledb;server=sql.jaslon.local;database=MLW3;integrated security=SSPI";
                        objBL.ErrorLogFile = "error.xml";
                        objBL.KeepIdentity = true;
                        objBL.Execute (@"..\..\SampleSchema.xml",@"..\..\SMALL_SAMPLE_SAIL_SCHED2WithData.XML");
                  }
                  catch(Exception e)
                  {
                        Console.WriteLine(e.ToString());
                  }



ERROR:

System.Runtime.InteropServices.COMException (0x80004005): No data was provided for column 'RoutingDetails_SailingId' on table 'tbl_SailingSchedule_RoutingDetails', and this column cannot contain NULL values.

   at SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute(String bstrSchemaFile, Obje
ct vDataFile)
   at SailingSchedule.Class1.InsertData() in d:\daniel documents and work\visual
 studio projects\nacalogistics\sailingschedule\class1.cs:line 52



Data: as above

Schema:

<?xml version="1.0" encoding="utf-8" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
      <xsd:annotation>
            <xsd:appinfo>
                  <sql:relationship name="FK_tbl_SailingSchedule_RoutingDetails_tbl_SailingSchedule"
                        parent="tbl_SailingSchedule"
                        parent-key="SailingSchedule_id"
                        child="tbl_SailingSchedule_RoutingDetails"
                        child-key="RoutingDetails_SailingId" />

            </xsd:appinfo>
      </xsd:annotation>

      <xsd:element name="ScheduleDetails" sql:relation="tbl_SailingSchedule" sql:key-fields="SailingSchedule_id" >
            <xsd:complexType>
                  <xsd:sequence>
                        <xsd:element name="VesselVoyageID"        type="xsd:string" />
                        <xsd:element name="VesselName"        type="xsd:string" />
                        <xsd:element name="IMONumber"        type="xsd:string" />
                        <xsd:element name="Voyage"        type="xsd:string" />
                        <xsd:element name="CarrierSCAC"        type="xsd:string" />
                        <xsd:element name="SCAC"        type="xsd:string" />
                        <xsd:element name="AmsFlag"        type="xsd:string" />
                        <xsd:element name="CFSOrigin"        type="xsd:string" />
                        <xsd:element name="CFSDestination"        type="xsd:string" />
                         <xsd:element name="RoutingDetails"
                              sql:relationship="FK_tbl_SailingSchedule_RoutingDetails_tbl_SailingSchedule"
                              sql:relation="tbl_SailingSchedule_RoutingDetails" sql:key-fields="RoutingDetails_id"
                         >
                              <xsd:complexType>
                                    <xsd:sequence maxOccurs="unbounded">
                                          <xsd:element name="StageQualifier"        type="xsd:string" />
                                          <xsd:element name="Transportmode"        type="xsd:string" />
                                          <xsd:element name="TransportName"        type="xsd:string" />
                                          <xsd:element name="IMONumber"        type="xsd:string" />
                                          <xsd:element name="Origin"        type="xsd:string" />
                                          <xsd:element name="ETD"        type="xsd:string" />
                                          <xsd:element name="Destination"        type="xsd:string" />
                                          <xsd:element name="ETA"        type="xsd:string" />
                                    </xsd:sequence>
                              </xsd:complexType>
                        </xsd:element>
                         
                  </xsd:sequence>
            </xsd:complexType>
      </xsd:element>







</xsd:schema>








 
0
 
jaswwAuthor Commented:
Please delete this question.
I asked it elsewhere and have received a satisfactory answer.  I can use SQLXML bulk load.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.