Load XML file into SQL Server 2000

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
PSIUnitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>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
PSIUnitAuthor Commented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.