title: How to fix Only one top level element is allowed in an XML document error when parsing into SQL
body: <p>I am attempting to construct an SQL Transaction that imports an XML file into the SQL server. However while doing so I get this error:</p>
  
  <blockquote>
    <p>The XML parse error 0xc00ce555 occurred on line number 1, near the XML text "13.992004-07-03Z1972-09-25ZS75001-100000M00Bachelors Professional1410+ Miles
    
    <p>Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 0]<br>
    The error description is 'Only one top level element is allowed in an XML document.'.</p>
    
    <p>Msg 8179, Level 16, State 5, Line 13<br>
    Could not find prepared statement with handle 0.</p>
    
    <p>Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1 [Batch Start Line 0]<br>
    sp_xml_removedocument: The value supplied for parameter number 1 is invalid.</p>
  </blockquote>
  
  <p>I tried wrapping all of the XML in a <code>&lt;root&gt;&lt;/root&gt;</code> tag as I was under the impression my XML file had multiple roots which I know is not allowed. However the file itself does not.</p>
  
  <p>Here is my SQL query</p>
  
  <pre><code>DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
  
  SELECT @XML = BulkColumn
  FROM OPENROWSET(BULK 'C:\Users\Slick\desktop\xmlTest.xml', SINGLE_BLOB) AS x;
  
  SELECT * 
  FROM AdventureWorks2016CTP3.Person.Person 
  WHERE FirstName = 'John'
  
  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
  
  SELECT FirstName
  FROM OPENXML(@hDoc, 'Person.Person BusinessEntityID="18334" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="C" LastName="Anderson" EmailPromotion="0" rowguid="B3E71FA8-890F-40A5-A36E-01D7CFCAF4F7" ModifiedDate="2014-06-02T00:00:00"')
  WITH
  (
      FirstName [VARCHAR](50) '@FirstName',
      BusinessEntityID [VARCHAR](100) '@BusinessEntityID'
  )
  
  EXEC sp_xml_removedocument @hDoc
  </code></pre>
  
  <p>Here is my XML File</p>
  
  <pre><code>&lt;Person.Person BusinessEntityID="18334" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="C" LastName="Anderson" EmailPromotion="0" rowguid="B3E71FA8-890F-40A5-A36E-01D7CFCAF4F7" ModifiedDate="2014-06-02T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;13.99&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2004-07-03Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1972-09-25Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;S&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;75001-100000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;0&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;0&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Bachelors &lt;/Education&gt;
        &lt;Occupation&gt;Professional&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;4&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;10+ Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="371" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Arthur" EmailPromotion="0" rowguid="902112B6-7FE7-4E85-8D77-FF49C5E6761A" ModifiedDate="2011-07-01T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-7693.8214&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="377" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="P." LastName="Ault" EmailPromotion="0" rowguid="F0B67A75-4235-45D5-A5E0-2E938A975DE8" ModifiedDate="2013-06-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-8648.19&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="429" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="A." LastName="Beaver" EmailPromotion="0" rowguid="011DD8D5-1AC4-4FE8-B139-EE9D0EA172E6" ModifiedDate="2013-02-28T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-131.2036&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="445" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="M." LastName="Bennetts" EmailPromotion="0" rowguid="694258A3-633C-42F9-98B8-08E50EDEDC68" ModifiedDate="2013-07-31T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-729.438&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="465" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="M." LastName="Berger" Suffix="Jr." EmailPromotion="0" rowguid="B901DD8D-087B-49BE-BF1F-3B11F6D95820" ModifiedDate="2011-07-01T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-11626.0099&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="475" PersonType="SC" NameStyle="0" FirstName="John" LastName="Berry" EmailPromotion="0" rowguid="8F36A32A-BC2C-4313-910C-E7A1E2D13045" ModifiedDate="2013-06-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-16463.544&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="533" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="R." LastName="Brooks" EmailPromotion="0" rowguid="4B21D63C-181F-404A-A1AB-03EDF4E1D478" ModifiedDate="2012-05-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-3947.6869&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18292" PersonType="IN" NameStyle="0" FirstName="John" LastName="Brown" EmailPromotion="0" rowguid="9D88EA61-6B2C-430F-9FFF-68D4107EB044" ModifiedDate="2013-10-12T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-9.99&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2003-11-13Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1956-06-10Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;M&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;50001-75000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;3&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;2&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Partial College&lt;/Education&gt;
        &lt;Occupation&gt;Professional&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;1&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;5-10 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="2105" PersonType="GC" NameStyle="0" FirstName="John" LastName="Campbell" EmailPromotion="0" rowguid="D9533653-5147-4F7D-A904-6E7801E50F91" ModifiedDate="2011-05-24T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="62" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="T" LastName="Campbell" EmailPromotion="1" rowguid="F5A4CC1B-AEFA-46CA-8888-2CA1C6D4034B" ModifiedDate="2014-02-07T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="2329" PersonType="GC" NameStyle="0" FirstName="John" LastName="Chen" EmailPromotion="1" rowguid="185EDBE3-5FEA-4FD7-A0C6-F1E26EE13FC1" ModifiedDate="2009-01-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="184" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="Y" LastName="Chen" EmailPromotion="2" rowguid="5E6EC398-C951-44A2-BEC7-DC1CD17FAACC" ModifiedDate="2009-02-02T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="679" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="Y." LastName="Chen" EmailPromotion="1" rowguid="D6E4BD36-001D-4286-BEC4-634E1886CBDE" ModifiedDate="2011-08-01T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18312" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="M" LastName="Clark" EmailPromotion="2" rowguid="EA8268DC-E26F-4B91-BA9E-8AA408DFE74C" ModifiedDate="2014-03-19T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;518.98&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2004-04-19Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1949-07-23Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;M&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;50001-75000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;2&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;1&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Partial College&lt;/Education&gt;
        &lt;Occupation&gt;Professional&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;0&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;0&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;2-5 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="723" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="L." LastName="Colon" EmailPromotion="0" rowguid="23030320-BA4C-47BA-A202-1D241D26EB83" ModifiedDate="2011-08-01T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-3355.6712&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18324" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="F" LastName="Davis" EmailPromotion="1" rowguid="C61627D2-CF14-4D3C-9658-1BEC0AE90CE1" ModifiedDate="2013-12-11T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;1666&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2004-01-12Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1969-10-16Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;M&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;75001-100000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;2&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;0&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Graduate Degree&lt;/Education&gt;
        &lt;Occupation&gt;Professional&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;0&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;0-1 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="819" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="T." LastName="Donovan" Suffix="Jr." EmailPromotion="1" rowguid="EC1FB547-F0C4-457E-8998-D3ECBD5A9A2C" ModifiedDate="2011-05-31T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-14892.8367&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="857" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Emory" EmailPromotion="1" rowguid="46D8141F-65D3-40EA-BE3D-8A1747C27923" ModifiedDate="2013-05-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-10584.318&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="873" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Evans" EmailPromotion="0" rowguid="C1C5773F-6FBC-4636-BF03-96E804CD6A7C" ModifiedDate="2012-05-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-3483.0418&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="112" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="P" LastName="Evans" EmailPromotion="1" rowguid="FCFA001C-D5BC-455F-9124-21290B4A5304" ModifiedDate="2008-12-25T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="913" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Ford" EmailPromotion="2" rowguid="7AB0363A-C2A5-46F6-8D54-49ED1237D8F2" ModifiedDate="2011-07-01T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-5505.6174&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="905" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Fredericksen" EmailPromotion="0" rowguid="AB39C90F-FB62-4C1E-B2BD-8378E5C306BB" ModifiedDate="2013-07-31T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-2030.73375&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="178" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="N" LastName="Frum" EmailPromotion="2" rowguid="CE13FF53-4465-4E3A-90DA-2FA0E8F19378" ModifiedDate="2009-02-24T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18205" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="M" LastName="Garcia" EmailPromotion="0" rowguid="EB954C20-77E0-4FC1-BD44-4D1BCC5FB0F1" ModifiedDate="2013-04-14T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;2434.3575&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2003-05-16Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1964-08-08Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;S&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;25001-50000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;2&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;2&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Partial College&lt;/Education&gt;
        &lt;Occupation&gt;Clerical&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;2&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;0-1 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="1047" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Hanson" EmailPromotion="1" rowguid="5C96AB93-1919-4CC7-A0FA-9AC1D5FF26B0" ModifiedDate="2012-08-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-3544.227&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18357" PersonType="IN" NameStyle="0" FirstName="John" LastName="Harris" EmailPromotion="1" rowguid="E6CEBBE9-E84F-442C-9011-3AE23A9FE42A" ModifiedDate="2013-09-20T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-45&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2003-10-22Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1944-04-26Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;M&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;50001-75000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;4&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;0&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Bachelors &lt;/Education&gt;
        &lt;Occupation&gt;Management&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;2&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;10+ Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18345" PersonType="IN" NameStyle="0" FirstName="John" LastName="Jackson" EmailPromotion="1" rowguid="86F5682C-FF40-48F5-AA76-741C10E4F61A" ModifiedDate="2013-09-26T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;2.7&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2003-10-28Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1978-03-17Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;M&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;25001-50000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;0&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;0&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Partial College&lt;/Education&gt;
        &lt;Occupation&gt;Skilled Manual&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;1&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;5-10 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18223" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="A" LastName="Johnson" EmailPromotion="0" rowguid="ADF22FBA-3349-4F53-B359-F082A2ADD550" ModifiedDate="2013-10-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;30.99&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2003-12-01Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1973-03-16Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;S&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;0-25000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;0&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;0&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;Partial High School&lt;/Education&gt;
        &lt;Occupation&gt;Manual&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;2&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;1-2 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="18251" PersonType="IN" NameStyle="0" FirstName="John" LastName="Jones" EmailPromotion="0" rowguid="09A2FA2B-6F0B-4B2F-B427-0D6577DEF017" ModifiedDate="2014-04-02T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;27.69&lt;/TotalPurchaseYTD&gt;
        &lt;DateFirstPurchase&gt;2004-05-03Z&lt;/DateFirstPurchase&gt;
        &lt;BirthDate&gt;1949-09-14Z&lt;/BirthDate&gt;
        &lt;MaritalStatus&gt;S&lt;/MaritalStatus&gt;
        &lt;YearlyIncome&gt;25001-50000&lt;/YearlyIncome&gt;
        &lt;Gender&gt;M&lt;/Gender&gt;
        &lt;TotalChildren&gt;4&lt;/TotalChildren&gt;
        &lt;NumberChildrenAtHome&gt;0&lt;/NumberChildrenAtHome&gt;
        &lt;Education&gt;High School&lt;/Education&gt;
        &lt;Occupation&gt;Professional&lt;/Occupation&gt;
        &lt;HomeOwnerFlag&gt;1&lt;/HomeOwnerFlag&gt;
        &lt;NumberCarsOwned&gt;2&lt;/NumberCarsOwned&gt;
        &lt;CommuteDistance&gt;5-10 Miles&lt;/CommuteDistance&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  &lt;/Person.Person&gt;
  &lt;Person.Person BusinessEntityID="1197" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Kane" EmailPromotion="1" rowguid="38A0FE20-40AB-47B9-9EEB-929D7E782E21" ModifiedDate="2013-05-30T00:00:00"&gt;
    &lt;Demographics&gt;
      &lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;
        &lt;TotalPurchaseYTD&gt;-6501.528&lt;/TotalPurchaseYTD&gt;
      &lt;/IndividualSurvey&gt;
    &lt;/Demographics&gt;
  ....
  &lt;/Person.Person&gt;
  </code></pre>
  
  <p>Stackoverflow won't let me post whole XML in body.</p>
  
  <p>I should get a SQL transaction code from SMSS</p>