Link to home
Start Free TrialLog in
Avatar of D B
D BFlag for United States of America

asked on

Loading XML data into SQL Server 2000

I have XML data that we are being feed daily and need to load it into SQL Server. I've done some basic stuff using the SQL XML Bulk Load object for single level files. However, I've got the following schema file and am not certain how it loads. Does it load into a single table, or do I need to create a table for each hierarchy level? If the latter, then how do I maintain relationships? This is VERY new to me.

The most appropriate way for me to envision doing this is to import into a single staging table and then parse as needed into the final tables (especially since some of the data contains strings like "Not Available" for a date value). Do I just map the schema to a single table and define all the columns for each element?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="HDMExport">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Device" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="IsActivated" type="xs:boolean" />
              <xs:element name="IsPreProvisioned" type="xs:boolean" />
              <xs:element name="InactiveFourteenDays" type="xs:boolean" />
              <xs:element name="PreProvisionDate" type="xs:string" />
              <xs:element name="FirstContactDate" type="xs:string" />
              <xs:element name="LastContactDate" type="xs:string" />
              <xs:element name="SerialNumber" type="xs:string" />
              <xs:element name="MACAddress" type="xs:string" />
              <xs:element name="ActivationError" type="xs:string" />
              <xs:element name="CustomerPMAInformation">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="OperationSuccessful" type="xs:boolean" />
                    <xs:element name="Account" type="xs:string" />
                    <xs:element name="AccountNumber" type="xs:string" />
                    <xs:element name="BillingPhoneNumber" type="xs:string" />
                    <xs:element name="PhoneNumber" type="xs:string" />
                    <xs:element name="CustomerName" type="xs:string" />
                    <xs:element name="State" type="xs:string" />
                    <xs:element name="ZIPCode" type="xs:string" />
                    <xs:element name="Company" type="xs:string" />
                    <xs:element name="Region" type="xs:string" />
                    <xs:element name="ClassService" type="xs:string" />
                    <xs:element name="ServiceCode" type="xs:string" />
                    <xs:element name="ProductCode" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="CPEReplacementHistory" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="ReplacementDate" type="xs:date" />
                    <xs:element name="Type" type="xs:string" />
                    <xs:element name="SerialNumber" type="xs:string" />
                    <xs:element name="ReplacementCount" type="xs:byte" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Open in new window

Avatar of cauos
cauos
try to not design flat table for all your data, because it will be hard to maintain  and repeated data, also make very hard to make report from your data. so you should make normalization for better database design.
when you mapping from xml file to database you should take in consideration many issues:
1- know what is the business logic of what you are going to make database design.
2- if you don't have enough information about the business logic; try to get the relationship from the xml structure

what i see from the xml file that you have three tables
1- Devices  table has those fields:
IsActivated
IsPreProvisioned
InactiveFourteenDays
PreProvisionDate
FirstContactDate
LastContactDate
SerialNumber
MACAddress
ActivationError
CustomerPMAInformationID
CPEReplacementHistory

>>>>the relationship between Devices table and CustomerPMAInformations  is one-to-one
2- CustomerPMAInformations table:
OperationSuccessful
Account
AccountNumber
BillingPhoneNumber
PhoneNumber
CustomerName
State
ZIPCode
Company
Region
ClassService
ServiceCode
ProductCode

>>>the relationship between Devices and CPEReplacementHistorys is one-to-many and you should make foreign primary key relationship; because the device may have 0 or more CPEReplacementHistorys (minOccurs="0" maxOccurs="unbounded")
3- CPEReplacementHistorys Table
ReplacementDate
type
SerialNumber
ReplacementCount
Avatar of D B
D B
Flag of United States of America image

ASKER

cauos: I hate to say it but your comment provided no help.

My question is more related to a mapping schema file for loading this data into SQL Server. I mentioned loading it into a single 'staging' table mainly for the purpose of properly mapping the columns after they have been imported. The data provider is, as I explained in my post, sending us such things as 'NOT AVAILABLE' as a value for a date. Obviously, I cannot load this into a date datatype column, but can stage it and then load NULL if the value is 'NOT AVAILABLE'

I understand the XSD I provided and understand that it represents three tables. Also, with XML being hierarchical and not relational, the XML data positionally represents the relationship. If I load this into 3 separate tables, then I've lost the relationship between them, unless there is something within the schema that allows me to generate a key that will exist between related elements.

As I said, on this subject I am a beginner. In fact, on a scale of 1 to 10 in the beginner bracket, I am a 1.
Avatar of cauos
cauos
i think i didn't get what exactly what you want. andi should checked your profile before made a comment;
also SQL XML Bulk is out of my information  scope
sorry my friend for commenting before known exactly what you need
Avatar of D B
D B
Flag of United States of America image

ASKER

Okay, a little more insight. Below is a schema mapping file for an XML file that has a single level of hierarchy. It maps the XML to the datatypes, the table and the columns in that table.

I've played around (very) little with SSIS in SQL Server 2005, and I know that you can map out each level to a table and SSIS will automatically generate a unique key for each record (parent and child) that will relate the data together. Can something like that be done in 2000? Basically, I need to load three tables (Device, CustomerPMAInformation and CPEReplacementHistory)
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
 
   <ElementType name="SHIP_DATE" dt:type="dateTime" />
   <ElementType name="PRODUCT_ID" dt:type="string" />
   <ElementType name="VNDR_PART" dt:type="string" />
   <ElementType name="DESCRIPTION" dt:type="string" />
   <ElementType name="SHIP_TO" dt:type="string" />
   <ElementType name="Z_NAME" dt:type="string" />
   <ElementType name="CUST_NAME" dt:type="string" />
   <ElementType name="ADDRESS1" dt:type="string" />
   <ElementType name="ADDRESS2" dt:type="string" />
   <ElementType name="ADDRESS3" dt:type="string" />
   <ElementType name="CITY" dt:type="string" />
   <ElementType name="STATE" dt:type="string" />
   <ElementType name="ZIP" dt:type="string" />
   <ElementType name="CUST_TEL" dt:type="long" />
   <ElementType name="SERIAL_NUM" dt:type="string" />
   <ElementType name="CUSTOMER_PO" dt:type="string" />
 
   <ElementType name="EMBARQ_LOGISTICS_DATA" sql:is-constant="1">
      <element type="INDIVIDUAL_ORDER" />
   </ElementType>
 
   <ElementType name="INDIVIDUAL_ORDER"  sql:relation="MOTIVE_DAILY">
      <element type="SHIP_DATE"  sql:field="ShipDate" />
      <element type="PRODUCT_ID" sql:field="ProductID" />
      <element type="VNDR_PART" sql:field="VendorPartNumber" />
      <element type="DESCRIPTION" sql:field="Description" />
      <element type="SHIP_TO" sql:field="ShipTo" />
      <element type="Z_NAME" sql:field="ZName" />
      <element type="CUST_NAME" sql:field="CustomerName" />
      <element type="ADDRESS1" sql:field="AddressLine1" />
      <element type="ADDRESS2" sql:field="AddressLine2" />
      <element type="ADDRESS3" sql:field="AddressLine3" />
      <element type="CITY" sql:field="City" />
      <element type="STATE" sql:field="State" />
      <element type="ZIP" sql:field="ZipCode" />
      <element type="CUST_TEL" sql:field="AccountNumber" />
      <element type="SERIAL_NUM" sql:field="SerialNumber" />
      <element type="CUSTOMER_PO" sql:field="CustomerPO" />
   </ElementType>
 
</Schema>

Open in new window

Avatar of D B
D B
Flag of United States of America image

ASKER

Okay, below is the latest mapping schem I've deveoped. I've pieced it together from various sites I've searched on the web. I need to figure out what is missing in the schema file. When I execute the SQLXMLBulkLoad object, I get the error:

    Schema mapping: Schema: relationship expected on 'Device'.

The error occurs on the Execute method of the object.
The code I am running is (connection string changed to protect the innocent):

  set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")
  objBL.ConnectionString = "provider=SQLOLEDB;data source=server;database=dtatbase;integrated security=SSPI"
  objBL.KeepIdentity = false
  objBL.ErrorLogFile = "c:\error.xml"
  objBL.Execute "C:\DataFeedResults.xsd","C:\DataFeedResults.xml"
  set objBL=Nothing

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns="http://tempuri.org/" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:dt="urn:schemas-microsoft-com:xml:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <xs:annotation>
    <xs:appinfo>
      <sql:relationship name="DevCust" parent="XML_Device" child="XML_CustomerPMAInformation" parent-key="DeviceID" child-key="DeviceID" />
      <sql:relationship name="DevRepl" parent="XML_Device" child="XML_CPEReplacementHistory" parent-key="DeviceID" child-key="DeviceID" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="HDMExport">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Device" minOccurs="0" maxOccurs="unbounded" sql:is-constant="true" sql:relation="XML_Device">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="IsActivated" type="xs:boolean" dt:type="bit" sql:field="IsActivated" />
              <xs:element minOccurs="0" name="IsPreProvisioned" dt:type="bit" type="xs:boolean" sql:field="IsPreProvisioned" />
              <xs:element minOccurs="0" name="InactiveFourteenDays" dt:type="bit" type="xs:boolean" sql:field="InactiveFourteenDays" />
              <xs:element minOccurs="0" name="PreProvisionDate" dt:type="string" type="xs:string" sql:field="PreProvisionedDate" />
              <xs:element minOccurs="0" name="FirstContactDate" dt:type="string" type="xs:string" sql:field="FirstContactDate" />
              <xs:element minOccurs="0" name="LastContactDate" dt:type="string" type="xs:string" sql:field="LastContactDate" />
              <xs:element minOccurs="0" name="SerialNumber" dt:type="string" type="xs:string" sql:field="SerialNumber" />
              <xs:element minOccurs="0" name="MACAddress" dt:type="string" type="xs:string" sql:field="MACAddress" />
              <xs:element minOccurs="0" name="ActivationError" dt:type="string" type="xs:string" sql:field="ActivationError" />
              <xs:element minOccurs="0" name="CustomerPMAInformation" sql:is-constant="true" sql:relation="XML_CustomerPMAInformation" sql:relationship="DevCust">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" name="OperationSuccessful" dt:type="bit" type="xs:boolean" sql:field="OperationSuccessful" />
                    <xs:element minOccurs="0" name="Account" dt:type="string" type="xs:string" sql:field="Account" />
                    <xs:element minOccurs="0" name="AccountNumber" dt:type="string" type="xs:string" sql:field="AccountNumber" />
                    <xs:element minOccurs="0" name="BillingPhoneNumber" dt:type="string" type="xs:string" sql:field="BillingPhoneNumber" />
                    <xs:element minOccurs="0" name="PhoneNumber" dt:type="string" type="xs:string" sql:field="PhoneNumber" />
                    <xs:element minOccurs="0" name="CustomerName" dt:type="string" type="xs:string" sql:field="CustomerName" />
                    <xs:element minOccurs="0" name="State" dt:type="string" type="xs:string" sql:field="State" />
                    <xs:element minOccurs="0" name="ZIPCode" dt:type="string" type="xs:string" sql:field="ZIPCode" />
                    <xs:element minOccurs="0" name="Company" dt:type="string" type="xs:string" sql:field="Company" />
                    <xs:element minOccurs="0" name="Region" dt:type="string" type="xs:string" sql:field="Region" />
                    <xs:element minOccurs="0" name="ClassService" dt:type="string" type="xs:string" sql:field="ClassOfService" />
                    <xs:element minOccurs="0" name="ServiceCode" dt:type="string" type="xs:string" sql:field="ServiceCode" />
                    <xs:element minOccurs="0" name="ProductCode" dt:type="string" type="xs:string" sql:field="ProductCode" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="CPEReplacementHistory" sql:is-constant="true" sql:relation="XML_CPEReplacementHistory" sql:relationship="DevRepl">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" name="ReplacementDate" dt:type="DateTime" type="xs:date" sql:field="ReplacementDate" />
                    <xs:element minOccurs="0" name="Type" dt:type="string" type="xs:string" sql:field="Type" />
                    <xs:element minOccurs="0" name="SerialNumber" dt:type="string" type="xs:string" sql:field="SerialNumber" />
                    <xs:element minOccurs="0" name="ReplacementCount" dt:type="Integer" type="xs:unsignedByte" sql:field="ReplacementCount" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of D B
D B
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial