Solved

Loading XML data into SQL Server 2000

Posted on 2009-05-04
6
287 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
  • 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now