Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Loading XML data into SQL Server 2000

Posted on 2009-05-04
6
Medium Priority
?
302 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:dbbishop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:cauos
ID: 24299393
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
0
 
LVL 15

Author Comment

by:dbbishop
ID: 24300073
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.
0
 
LVL 4

Expert Comment

by:cauos
ID: 24302390
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Author Comment

by:dbbishop
ID: 24306599
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

0
 
LVL 15

Author Comment

by:dbbishop
ID: 24307701
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

0
 
LVL 15

Accepted Solution

by:
dbbishop earned 0 total points
ID: 24308469
I was abe to solve this. If anyone is interested, here is the file schema file:
<?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:mapping-schema">
  <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" sql:is-constant="true" >
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Device" minOccurs="0" maxOccurs="unbounded" sql:key-fields="DeviceID" 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:relation="XML_CustomerPMAInformation" sql:relationship="DevCust" sql:key-fields="CustomerID">
                <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:relation="XML_CPEReplacementHistory" sql:relationship="DevRepl" sql:key-fields="ReplacementID">
                <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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question