Solved

Loading XML data into SQL Server 2000

Posted on 2009-05-04
6
300 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

627 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