Solved

Load XML file into SQL Server 2000

Posted on 2007-04-03
7
606 Views
Last Modified: 2008-06-28
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("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=myserver;database=mydatabase;Trusted_Connection=Yes"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "\\corp\lsw\xfer\format_files\NIPR_pdbbatch_schema.xml", "\\corp\lsw\xfer\import\NIPR_pdbbatch.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-msdata">
  <xs:element name="SCB_Report" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <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_List" 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_Code" type="xs:string" />
                              <xs:attribute name="LOA_Status_Reason_Date" 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_Effective" 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_Date" 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_Number" 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
0
Comment
Question by:PSIUnit
  • 4
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18845273
>>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?<<
A simpler way (not necessarily better) is to pass the contents of the Xml document into the stored procedure and
use OPENXML() to insert the rows into all the tables.
0
 

Author Comment

by:PSIUnit
ID: 18926659
Thank you for your reply.  I'm sorry it's taken so long to get back to this.  I have done some research on OPENXML() and it seems that it would work fine, but I have no idea what I'm doing.  I can't find any examples of exactly what I want to do or a good tutorial on it either.  This is a pretty complicated file and I'm not sure how to proceed.  The file is huge, but I copied it and left enough for you to get an idea of the parent/child relationships.  Can you help point me in the right direction on how to write this code?  When I try just to do the basic OPENXML using the sp_xml_preparedocument, it gives me an error - Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 43
XML parsing error: A name was started with an invalid character.  

I'm not really sure how to write it either to pull the data the way that I need.  I need to be able to load this data into tables where I can see the relationship between the data.  

Here is an example of the xml file that I'm working with:

<?xml version="1.0" ?>
  <!DOCTYPE SCB_Report (View Source for full doctype...)>
 <SCB_Report>
  <SCB_Report_Header Report_Type="Company Batch Report" Title="132808480" />
 <SCB_Report_Body>
 <SCB_Producer National_Producer_Number="38494" Secondary_ID="132808480">
 <Name_Birth Primary_Name="MERRILL LYNCH LIFE AGENCY INC" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" Date_Of_Birth="">
  <Other_Name State_Code="AK" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="F9489" />
  <Other_Name State_Code="AL" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="A239469" />
  <Other_Name State_Code="AR" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="206154" />
<Address State_Code="AK" Address_Type_Code="2" Address_Type="Business" Address_Status="Current" Date_Updated="02/22/2006" Address_Line1="1215 FOURTH AVE STE 2600" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161" Country="" State_Id="F9489" />
  <Address State_Code="AK" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current" Date_Updated="06/27/2006" Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161" Country="" State_Id="F9489" />
  <Address State_Code="AL" Address_Type_Code="2" Address_Type="Business" Address_Status="Current" Date_Updated="09/14/2005" Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161" Country="" State_Id="A239469" />
  <Address State_Code="AL" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current" Date_Updated="09/14/2005" Address_Line1="P O BOX 9060" Address_Line2="" Address_Line3="" City="PRINCETON" State="NJ" Zip_Code="08543" Country="" State_Id="A239469" />
  <License State_Code="MT" Class="Agency" Date_Status_Effective="06/06/2000" License_Class_Code="4" License_Number="AL902212" License_Status="Y" Resident_Indicator="N" License_Issue_Date="06/06/2000" License_Expiration_Date="" State_Id="">
  <LOA LOA_Name="Disability" LOA_Code="5" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
  <LOA LOA_Name="Life" LOA_Code="16" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
  <LOA LOA_Name="Variable Contracts" LOA_Code="8" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Variable Annuity" LOA_Code="90" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Variable Life" LOA_Code="91" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
  <Appointment Company_Name="Phoenix Life Ins Co" FEIN_Cocode="060493340" FEIN="060493340" COCODE="67814" Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason="" Status_Reason_Date="10/14/2005" Renewal_Date="" />
  <License State_Code="VA" Class="Agency" Date_Status_Effective="03/14/1977" License_Class_Code="4" License_Number="132808480007" License_Status="Y" Resident_Indicator="N" License_Issue_Date="03/14/1977" License_Expiration_Date="" State_Id="">
  <LOA LOA_Name="Life and Annuities" LOA_Code="327" LOA_Issue_Date="03/14/1977" LOA_Status="Active" LOA_Status_Reason="Licensed" LOA_Status_Reason_Code="174" LOA_Status_Reason_Date="03/14/1977" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
  <Appointment Company_Name="John Hancock Life Ins Co U S A" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Life & Health" LOA_Code="42" Status="Appointed" Termination_Reason="" Status_Reason_Date="08/30/1993" Renewal_Date="" />
  <Appointment Company_Name="Unum Life Ins Co Of America" FEIN_Cocode="010278678" FEIN="010278678" COCODE="62235" Line_Of_Authority="Life & Health" LOA_Code="42" Status="Appointed" Termination_Reason="" Status_Reason_Date="10/04/1990" Renewal_Date="" />
  <License State_Code="IA" Class="Producer" Date_Status_Effective="01/03/2005" License_Class_Code="3" License_Number="1001448" License_Status="Y" Resident_Indicator="N" License_Issue_Date="04/08/2002" License_Expiration_Date="03/31/2008" State_Id="">
  <LOA LOA_Name="Determined by Affiliated Prod" LOA_Code="238" LOA_Issue_Date="04/08/2002" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="01/03/2005" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
  </License>
  <RIRS State_Code="NY" Action_Id="11041160" Entity_Role="Unknown" Origin_Of_Action="ROUTINE DEPT ACTION" Reason_For_Action="MISSTATEMENT ON APPLICATION" Disposition="MONETARY PENALTY" Date_Of_Action="11/11/2005" Effective_Date="11/11/2005" Enter_Date="01/03/2006" File_Ref="1687" Penalty="500" Length_Of_Order="0" State_RIRS_Id="1R_11041160" State_Id="" />
  </SCB_Producer>
  <SCB_Partial_Find_List />
  </SCB_Report_Body>
  </SCB_Report>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18928641
The first thing you need is a well-formed Xml document.  Yours is not.  See here:
<?xml version="1.0" ?>
<SCB_Report>
      <SCB_Report_Header Report_Type="Company Batch Report" Title="132808480" />
      <SCB_Report_Body>
            <SCB_Producer National_Producer_Number="38494" Secondary_ID="132808480">
                  <Name_Birth Primary_Name="MERRILL LYNCH LIFE AGENCY INC" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" Date_Of_Birth=""/>
                  <Other_Name State_Code="AK" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="F9489" />
                  <Other_Name State_Code="AL" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="A239469" />
                  <Other_Name State_Code="AR" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="206154" />
                  <Address State_Code="AK" Address_Type_Code="2" Address_Type="Business" Address_Status="Current" Date_Updated="02/22/2006" Address_Line1="1215 FOURTH AVE STE 2600" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161" Country="" State_Id="F9489" />
                  <Address State_Code="AK" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current" Date_Updated="06/27/2006" Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161" Country="" State_Id="F9489" />
                  <Address State_Code="AL" Address_Type_Code="2" Address_Type="Business" Address_Status="Current" Date_Updated="09/14/2005" Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161" Country="" State_Id="A239469" />
                  <Address State_Code="AL" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current" Date_Updated="09/14/2005" Address_Line1="P O BOX 9060" Address_Line2="" Address_Line3="" City="PRINCETON" State="NJ" Zip_Code="08543" Country="" State_Id="A239469" />
                  <License State_Code="MT" Class="Agency" Date_Status_Effective="06/06/2000" License_Class_Code="4" License_Number="AL902212" License_Status="Y" Resident_Indicator="N" License_Issue_Date="06/06/2000" License_Expiration_Date="" State_Id=""/>
                  <LOA LOA_Name="Disability" LOA_Code="5" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <LOA LOA_Name="Life" LOA_Code="16" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <LOA LOA_Name="Variable Contracts" LOA_Code="8" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Variable Annuity" LOA_Code="90" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Variable Life" LOA_Code="91" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
                  <Appointment Company_Name="Phoenix Life Ins Co" FEIN_Cocode="060493340" FEIN="060493340" COCODE="67814" Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason="" Status_Reason_Date="10/14/2005" Renewal_Date="" />
                  <License State_Code="VA" Class="Agency" Date_Status_Effective="03/14/1977" License_Class_Code="4" License_Number="132808480007" License_Status="Y" Resident_Indicator="N" License_Issue_Date="03/14/1977" License_Expiration_Date="" State_Id=""/>
                  <LOA LOA_Name="Life and Annuities" LOA_Code="327" LOA_Issue_Date="03/14/1977" LOA_Status="Active" LOA_Status_Reason="Licensed" LOA_Status_Reason_Code="174" LOA_Status_Reason_Date="03/14/1977" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <Appointment Company_Name="John Hancock Life Ins Co U S A" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838" Line_Of_Authority="Life &amp; Health" LOA_Code="42" Status="Appointed" Termination_Reason="" Status_Reason_Date="08/30/1993" Renewal_Date="" />
                  <Appointment Company_Name="Unum Life Ins Co Of America" FEIN_Cocode="010278678" FEIN="010278678" COCODE="62235" Line_Of_Authority="Life &amp; Health" LOA_Code="42" Status="Appointed" Termination_Reason="" Status_Reason_Date="10/04/1990" Renewal_Date="" />
                  <License State_Code="IA" Class="Producer" Date_Status_Effective="01/03/2005" License_Class_Code="3" License_Number="1001448" License_Status="Y" Resident_Indicator="N" License_Issue_Date="04/08/2002" License_Expiration_Date="03/31/2008" State_Id=""/>
                  <LOA LOA_Name="Determined by Affiliated Prod" LOA_Code="238" LOA_Issue_Date="04/08/2002" LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="01/03/2005" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <RIRS State_Code="NY" Action_Id="11041160" Entity_Role="Unknown" Origin_Of_Action="ROUTINE DEPT ACTION" Reason_For_Action="MISSTATEMENT ON APPLICATION" Disposition="MONETARY PENALTY" Date_Of_Action="11/11/2005" Effective_Date="11/11/2005" Enter_Date="01/03/2006" File_Ref="1687" Penalty="500" Length_Of_Order="0" State_RIRS_Id="1R_11041160" State_Id="" />
            </SCB_Producer>
            <SCB_Partial_Find_List />
      </SCB_Report_Body>
</SCB_Report>

Notice the way that all tags are closed and special characters (<, &, etc) are escaped.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18928653
This might be tad easier to read:

<?xml version="1.0" ?>
<SCB_Report>
      <SCB_Report_Header Report_Type="Company Batch Report" Title="132808480" />
      <SCB_Report_Body>
            <SCB_Producer National_Producer_Number="38494" Secondary_ID="132808480">
                  <Name_Birth Primary_Name="MERRILL LYNCH LIFE AGENCY INC" First_Name="" Middle_Name="" Last_Name=""
                        Name_Suffix="" Date_Of_Birth=""/>
                  <Other_Name State_Code="AK" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name=""
                        Name_Suffix="" State_Id="F9489" />
                  <Other_Name State_Code="AL" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name=""
                        Name_Suffix="" State_Id="A239469" />
                  <Other_Name State_Code="AR" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias" First_Name="" Middle_Name="" Last_Name=""
                        Name_Suffix="" State_Id="206154" />
                  <Address State_Code="AK" Address_Type_Code="2" Address_Type="Business" Address_Status="Current" Date_Updated="02/22/2006"
                        Address_Line1="1215 FOURTH AVE STE 2600" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161"
                        Country="" State_Id="F9489" />
                  <Address State_Code="AK" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current" Date_Updated="06/27/2006"
                        Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161"
                        Country="" State_Id="F9489" />
                  <Address State_Code="AL" Address_Type_Code="2" Address_Type="Business" Address_Status="Current" Date_Updated="09/14/2005"
                        Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2="" Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161"
                        Country="" State_Id="A239469" />
                  <Address State_Code="AL" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current" Date_Updated="09/14/2005"
                        Address_Line1="P O BOX 9060" Address_Line2="" Address_Line3="" City="PRINCETON" State="NJ" Zip_Code="08543"
                        Country="" State_Id="A239469" />
                  <License State_Code="MT" Class="Agency" Date_Status_Effective="06/06/2000" License_Class_Code="4"
                        License_Number="AL902212" License_Status="Y" Resident_Indicator="N" License_Issue_Date="06/06/2000"
                        License_Expiration_Date="" State_Id=""/>
                  <LOA LOA_Name="Disability" LOA_Code="5" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason=""
                        LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <LOA LOA_Name="Life" LOA_Code="16" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason=""
                        LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <LOA LOA_Name="Variable Contracts" LOA_Code="8" LOA_Issue_Date="06/06/2000" LOA_Status="Active" LOA_Status_Reason=""
                        LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838"
                        Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002"
                        Renewal_Date="" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838"
                        Line_Of_Authority="Variable Annuity" LOA_Code="90" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002"
                        Renewal_Date="" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838"
                        Line_Of_Authority="Variable Life" LOA_Code="91" Status="Appointed" Termination_Reason="" Status_Reason_Date="01/01/2002"
                        Renewal_Date="" />
                  <Appointment Company_Name="Phoenix Life Ins Co" FEIN_Cocode="060493340" FEIN="060493340" COCODE="67814"
                        Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason="" Status_Reason_Date="10/14/2005"
                        Renewal_Date="" />
                  <License State_Code="VA" Class="Agency" Date_Status_Effective="03/14/1977" License_Class_Code="4" License_Number="132808480007"
                        License_Status="Y" Resident_Indicator="N" License_Issue_Date="03/14/1977" License_Expiration_Date="" State_Id=""/>
                  <LOA LOA_Name="Life and Annuities" LOA_Code="327" LOA_Issue_Date="03/14/1977" LOA_Status="Active" LOA_Status_Reason="Licensed"
                        LOA_Status_Reason_Code="174" LOA_Status_Reason_Date="03/14/1977" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <Appointment Company_Name="John Hancock Life Ins Co U S A" FEIN_Cocode="010233346" FEIN="010233346" COCODE="65838"
                        Line_Of_Authority="Life &amp; Health" LOA_Code="42" Status="Appointed" Termination_Reason="" Status_Reason_Date="08/30/1993"
                        Renewal_Date="" />
                  <Appointment Company_Name="Unum Life Ins Co Of America" FEIN_Cocode="010278678" FEIN="010278678" COCODE="62235"
                        Line_Of_Authority="Life &amp; Health" LOA_Code="42" Status="Appointed" Termination_Reason="" Status_Reason_Date="10/04/1990"
                        Renewal_Date="" />
                  <License State_Code="IA" Class="Producer" Date_Status_Effective="01/03/2005" License_Class_Code="3" License_Number="1001448"
                        License_Status="Y" Resident_Indicator="N" License_Issue_Date="04/08/2002" License_Expiration_Date="03/31/2008" State_Id=""/>
                  <LOA LOA_Name="Determined by Affiliated Prod" LOA_Code="238" LOA_Issue_Date="04/08/2002" LOA_Status="Active" LOA_Status_Reason=""
                        LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="01/03/2005" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <RIRS State_Code="NY" Action_Id="11041160" Entity_Role="Unknown" Origin_Of_Action="ROUTINE DEPT ACTION"
                        Reason_For_Action="MISSTATEMENT ON APPLICATION" Disposition="MONETARY PENALTY" Date_Of_Action="11/11/2005"
                        Effective_Date="11/11/2005" Enter_Date="01/03/2006" File_Ref="1687" Penalty="500" Length_Of_Order="0"
                        State_RIRS_Id="1R_11041160" State_Id="" />
            </SCB_Producer>
            <SCB_Partial_Find_List />
      </SCB_Report_Body>
</SCB_Report>

Once you have a well-formed document, you will find that the sp_xml_preparedocument functions as advertised.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18928764
Here is an complete example how you could get all the "Address" nodes:

Declare @XmlDoc varchar(8000)

Set @XmlDoc =
'<?xml version="1.0" ?>
<SCB_Report>
      <SCB_Report_Header Report_Type="Company Batch Report" Title="132808480" />
      <SCB_Report_Body>
            <SCB_Producer National_Producer_Number="38494" Secondary_ID="132808480">
                  <Name_Birth Primary_Name="MERRILL LYNCH LIFE AGENCY INC" First_Name="" Middle_Name=""
                        Last_Name="" Name_Suffix="" Date_Of_Birth=""/>
                  <Other_Name State_Code="AK" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias"
                        First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="F9489" />
                  <Other_Name State_Code="AL" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias"
                        First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="A239469" />
                  <Other_Name State_Code="AR" Name="MERRILL LYNCH LIFE AGENCY INC" Name_Type="Alias"
                        First_Name="" Middle_Name="" Last_Name="" Name_Suffix="" State_Id="206154" />
                  <Address State_Code="AK" Address_Type_Code="2" Address_Type="Business" Address_Status="Current"
                        Date_Updated="02/22/2006" Address_Line1="1215 FOURTH AVE STE 2600" Address_Line2=""
                        Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161"      Country="" State_Id="F9489" />
                  <Address State_Code="AK" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current"
                        Date_Updated="06/27/2006" Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2=""
                        Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161"      Country="" State_Id="F9489" />
                  <Address State_Code="AL" Address_Type_Code="2" Address_Type="Business" Address_Status="Current"
                        Date_Updated="09/14/2005" Address_Line1="1215 FOURTH AVE 26 FL" Address_Line2=""
                        Address_Line3="" City="SEATTLE" State="WA" Zip_Code="98161"      Country="" State_Id="A239469" />
                  <Address State_Code="AL" Address_Type_Code="3" Address_Type="Mailing" Address_Status="Current"
                        Date_Updated="09/14/2005" Address_Line1="P O BOX 9060" Address_Line2="" Address_Line3=""
                        City="PRINCETON" State="NJ" Zip_Code="08543" Country="" State_Id="A239469" />
                  <License State_Code="MT" Class="Agency" Date_Status_Effective="06/06/2000" License_Class_Code="4"
                        License_Number="AL902212" License_Status="Y" Resident_Indicator="N"
                        License_Issue_Date="06/06/2000"      License_Expiration_Date="" State_Id=""/>
                  <LOA LOA_Name="Disability" LOA_Code="5" LOA_Issue_Date="06/06/2000" LOA_Status="Active"
                        LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000"
                        CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <LOA LOA_Name="Life" LOA_Code="16" LOA_Issue_Date="06/06/2000" LOA_Status="Active"
                        LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000"
                        CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <LOA LOA_Name="Variable Contracts" LOA_Code="8" LOA_Issue_Date="06/06/2000" LOA_Status="Active"
                        LOA_Status_Reason="" LOA_Status_Reason_Code="0" LOA_Status_Reason_Date="06/06/2000"
                        CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346"
                        COCODE="65838" Line_Of_Authority="Life" LOA_Code="16" Status="Appointed"
                        Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346"
                        COCODE="65838" Line_Of_Authority="Variable Annuity" LOA_Code="90" Status="Appointed"
                        Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
                  <Appointment Company_Name="John Hancock Life Ins Co Usa" FEIN_Cocode="010233346" FEIN="010233346"
                        COCODE="65838" Line_Of_Authority="Variable Life" LOA_Code="91" Status="Appointed"
                        Termination_Reason="" Status_Reason_Date="01/01/2002" Renewal_Date="" />
                  <Appointment Company_Name="Phoenix Life Ins Co" FEIN_Cocode="060493340" FEIN="060493340"
                        COCODE="67814" Line_Of_Authority="Life" LOA_Code="16" Status="Appointed" Termination_Reason=""
                        Status_Reason_Date="10/14/2005"      Renewal_Date="" />
                  <License State_Code="VA" Class="Agency" Date_Status_Effective="03/14/1977" License_Class_Code="4"
                        License_Number="132808480007" License_Status="Y" Resident_Indicator="N"
                        License_Issue_Date="03/14/1977" License_Expiration_Date="" State_Id=""/>
                  <LOA LOA_Name="Life and Annuities" LOA_Code="327" LOA_Issue_Date="03/14/1977" LOA_Status="Active"
                        LOA_Status_Reason="Licensed" LOA_Status_Reason_Code="174" LOA_Status_Reason_Date="03/14/1977"
                        CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <Appointment Company_Name="John Hancock Life Ins Co U S A" FEIN_Cocode="010233346" FEIN="010233346"
                        COCODE="65838" Line_Of_Authority="Life &amp; Health" LOA_Code="42" Status="Appointed"
                        Termination_Reason="" Status_Reason_Date="08/30/1993" Renewal_Date="" />
                  <Appointment Company_Name="Unum Life Ins Co Of America" FEIN_Cocode="010278678" FEIN="010278678"
                        COCODE="62235" Line_Of_Authority="Life &amp; Health" LOA_Code="42" Status="Appointed"
                        Termination_Reason="" Status_Reason_Date="10/04/1990" Renewal_Date="" />
                  <License State_Code="IA" Class="Producer" Date_Status_Effective="01/03/2005" License_Class_Code="3"
                        License_Number="1001448" License_Status="Y" Resident_Indicator="N" License_Issue_Date="04/08/2002"
                        License_Expiration_Date="03/31/2008" State_Id=""/>
                  <LOA LOA_Name="Determined by Affiliated Prod" LOA_Code="238" LOA_Issue_Date="04/08/2002"
                        LOA_Status="Active" LOA_Status_Reason="" LOA_Status_Reason_Code="0"
                        LOA_Status_Reason_Date="01/03/2005" CE_Compliance="S" CE_Renewal_Date="" CE_Credits_Needed="0" />
                  <RIRS State_Code="NY" Action_Id="11041160" Entity_Role="Unknown" Origin_Of_Action="ROUTINE DEPT ACTION"
                        Reason_For_Action="MISSTATEMENT ON APPLICATION" Disposition="MONETARY PENALTY"
                        Date_Of_Action="11/11/2005"      Effective_Date="11/11/2005" Enter_Date="01/03/2006"
                        File_Ref="1687" Penalty="500" Length_Of_Order="0" State_RIRS_Id="1R_11041160" State_Id="" />
            </SCB_Producer>
            <SCB_Partial_Find_List />
      </SCB_Report_Body>
</SCB_Report>'

Declare @iDoc int

Exec sp_xml_preparedocument @iDoc OUTPUT, @XmlDoc
Select      State_Code,
            Address_Type_Code,
            Address_Type,
            Address_Status,
            Date_Updated,
            Address_Line1,
            Address_Line2,
            Address_Line3,
            City,
            State,
            Zip_Code,
            Country,
            State_Id
From      OPENXML(@iDoc, 'SCB_Report/SCB_Report_Body/SCB_Producer/Address', 1) WITH (
                  State_Code char(2) '@State_Code',
                  Address_Type_Code tinyint '@Address_Type_Code',
                  Address_Type varchar(20) '@Address_Type',
                  Address_Status varchar(20) '@Address_Status',
                  Date_Updated smalldatetime '@Date_Updated',
                  Address_Line1 varchar(50) '@Address_Line1',
                  Address_Line2 varchar(50) '@Address_Line2',
                  Address_Line3 varchar(50) '@Address_Line3',
                  City varchar(50) '@City',
                  State char(2) '@State',
                  Zip_Code char(5) '@Zip_Code',
                  Country varchar(20) '@Country',
                  State_Id char(2) '@State_Id')

Exec sp_xml_removedocument @iDoc

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

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

12 Experts available now in Live!

Get 1:1 Help Now