I've been researching this situation for about a week, so any help that anyone could provide would be very much appreciated because I am beyond frustrated. Before last week I knew absolutely nothing about xml. The only files that I've loaded into my SQL Server have been .txt and .csv, so the xml format is way different.
I've got an xml file that I need to take the data and load it into SQL Server 2000. After researching, the way that makes most sense to me to load this data (and the example that I've gotten to work) is to have a VB script that uses the SQLXMLBulkLoad utility to load the data using a schema file that from what I can tell maps the data to the appropriate fields in the database. So far, I created a schema file just of the xml file by using these instructions that I found on Experts:
1) Put your XML file in an easy to find location (e.g. C:\temp\ )
2) Open Visual Studio Command Prompt (start> all programs> Visual Studio> Visual Studio tools> Visual Studio Command prompt)
3) enter: CD C:\temp
4) enter: XSD MyXmlFile.xml
This worked very well and I used the layout to create my SQL tables. And I've got a VB script from an example that I found that runs the SQLXMLBulkLoad utility:
Set objBL = CreateObject("SQLXMLBulkLo
ad.SQLXMLB
ulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=myserver;database=m
ydatabase;
Trusted_Co
nnection=Y
es"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "\\corp\lsw\xfer\format_fi
les\NIPR_p
dbbatch_sc
hema.xml",
"\\corp\lsw\xfer\import\NI
PR_pdbbatc
h.xml"
Set objBL = Nothing
My problem is that I do not know how to make a good schema file. I can't find a good tutorial that explains how to do exactly what I'm trying to do. The file is very complicated and will result, apparently, in 10 separate tables. The tables that I've created mimic the schema file:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="SCB_Report" xmlns="" xmlns:xs="
http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-
microsoft-
com:xml-ms
data">
<xs:element name="SCB_Report" msdata:IsDataSet="true" msdata:UseCurrentLocale="t
rue">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="SCB_Report_Header">
<xs:complexType>
<xs:attribute name="Report_Type" type="xs:string" />
<xs:attribute name="Title" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="SCB_Report_Body">
<xs:complexType>
<xs:sequence>
<xs:element name="SCB_Partial_Find_Lis
t" type="xs:string" minOccurs="0" />
<xs:element name="SCB_Producer" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Name_Birth" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Other_Name" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="State_Code" type="xs:string" />
<xs:attribute name="Name" type="xs:string" />
<xs:attribute name="Name_Type" type="xs:string" />
<xs:attribute name="First_Name" type="xs:string" />
<xs:attribute name="Middle_Name" type="xs:string" />
<xs:attribute name="Last_Name" type="xs:string" />
<xs:attribute name="Name_Suffix" type="xs:string" />
<xs:attribute name="State_Id" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Primary_Name" type="xs:string" />
<xs:attribute name="First_Name" type="xs:string" />
<xs:attribute name="Middle_Name" type="xs:string" />
<xs:attribute name="Last_Name" type="xs:string" />
<xs:attribute name="Name_Suffix" type="xs:string" />
<xs:attribute name="Date_Of_Birth" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="Address" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="State_Code" type="xs:string" />
<xs:attribute name="Address_Type_Code" type="xs:string" />
<xs:attribute name="Address_Type" type="xs:string" />
<xs:attribute name="Address_Status" type="xs:string" />
<xs:attribute name="Date_Updated" type="xs:string" />
<xs:attribute name="Address_Line1" type="xs:string" />
<xs:attribute name="Address_Line2" type="xs:string" />
<xs:attribute name="Address_Line3" type="xs:string" />
<xs:attribute name="City" type="xs:string" />
<xs:attribute name="State" type="xs:string" />
<xs:attribute name="Zip_Code" type="xs:string" />
<xs:attribute name="Country" type="xs:string" />
<xs:attribute name="State_Id" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="License" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="LOA" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="LOA_Name" type="xs:string" />
<xs:attribute name="LOA_Code" type="xs:string" />
<xs:attribute name="LOA_Issue_Date" type="xs:string" />
<xs:attribute name="LOA_Status" type="xs:string" />
<xs:attribute name="LOA_Status_Reason" type="xs:string" />
<xs:attribute name="LOA_Status_Reason_Co
de" type="xs:string" />
<xs:attribute name="LOA_Status_Reason_Da
te" type="xs:string" />
<xs:attribute name="CE_Compliance" type="xs:string" />
<xs:attribute name="CE_Renewal_Date" type="xs:string" />
<xs:attribute name="CE_Credits_Needed" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="Appointment" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="Company_Name" type="xs:string" />
<xs:attribute name="FEIN_Cocode" type="xs:string" />
<xs:attribute name="FEIN" type="xs:string" />
<xs:attribute name="COCODE" type="xs:string" />
<xs:attribute name="Line_Of_Authority" type="xs:string" />
<xs:attribute name="LOA_Code" type="xs:string" />
<xs:attribute name="Status" type="xs:string" />
<xs:attribute name="Termination_Reason" type="xs:string" />
<xs:attribute name="Status_Reason_Date" type="xs:string" />
<xs:attribute name="Renewal_Date" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="State_Code" type="xs:string" />
<xs:attribute name="Class" type="xs:string" />
<xs:attribute name="Date_Status_Effectiv
e" type="xs:string" />
<xs:attribute name="License_Class_Code" type="xs:string" />
<xs:attribute name="License_Number" type="xs:string" />
<xs:attribute name="License_Status" type="xs:string" />
<xs:attribute name="Resident_Indicator" type="xs:string" />
<xs:attribute name="License_Issue_Date" type="xs:string" />
<xs:attribute name="License_Expiration_D
ate" type="xs:string" />
<xs:attribute name="State_Id" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="RIRS" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="State_Code" type="xs:string" />
<xs:attribute name="Action_Id" type="xs:string" />
<xs:attribute name="Entity_Role" type="xs:string" />
<xs:attribute name="Origin_Of_Action" type="xs:string" />
<xs:attribute name="Reason_For_Action" type="xs:string" />
<xs:attribute name="Disposition" type="xs:string" />
<xs:attribute name="Date_Of_Action" type="xs:string" />
<xs:attribute name="Effective_Date" type="xs:string" />
<xs:attribute name="Enter_Date" type="xs:string" />
<xs:attribute name="File_Ref" type="xs:string" />
<xs:attribute name="Penalty" type="xs:string" />
<xs:attribute name="Length_Of_Order" type="xs:string" />
<xs:attribute name="State_RIRS_Id" type="xs:string" />
<xs:attribute name="State_Id" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="National_Producer_Nu
mber" type="xs:string" />
<xs:attribute name="Secondary_ID" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
So my question is, can someone show me how to make a schema file in the format that I need or is there a better way altogether to accomplish my objective?
Thanks,
Lena
Start Free Trial
