title: XML Shredding into SQL Server
body: <p>I have an XML doc in the following format. </p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;Survey xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;Name&gt;US CDB GI MMPS &lt;/Name&gt;
    &lt;Vendor Name="TWDS" /&gt;
    &lt;Versions&gt;
      &lt;Version Name="All Functions Incumbent Weighted Accouting Value" Year="2014"&gt;
        &lt;JobMasters&gt;
          &lt;JobMaster JobCode="AAS000-ALL-M1" JobTitle="AAS000 Administrative Services Generalist/Multidiscipline"&gt;
            &lt;Levels&gt;
              &lt;Level LevelCode="M1"&gt;
                &lt;JobDetails&gt;
                  &lt;JobDetail Scope="Global Revenue"&gt;
                    &lt;PayComponents&gt;
                      &lt;PayComponent Id="1" Name="Base" CompanyCount="16" IncumbentCount="281" IsPercent="false"&gt;
                        &lt;Type&gt;Base&lt;/Type&gt;
                        &lt;Pays&gt;
                          &lt;Pay Id="100" Stat="Average" Value="65.6"&gt;
                            &lt;StatValue&gt;Average&lt;/StatValue&gt;
                          &lt;/Pay&gt;
                          &lt;Pay Id="10" Stat="P10" Value="41"&gt;
                            &lt;StatValue&gt;P10&lt;/StatValue&gt;
                          &lt;/Pay&gt;
                          &lt;Pay Id="25" Stat="P25" Value="51.6"&gt;
                            &lt;StatValue&gt;P25&lt;/StatValue&gt;
                          &lt;/Pay&gt;
                          &lt;Pay Id="101" Stat="Median" Value="66.3"&gt;
                            &lt;StatValue&gt;Median&lt;/StatValue&gt;
                          &lt;/Pay&gt;
                          &lt;Pay Id="75" Stat="P75" Value="74.2"&gt;
                            &lt;StatValue&gt;P75&lt;/StatValue&gt;
                          &lt;/Pay&gt;
                          &lt;Pay Id="90" Stat="P90" Value="89.5"&gt;
                            &lt;StatValue&gt;P90&lt;/StatValue&gt;
                          &lt;/Pay&gt;
                        &lt;/Pays&gt;
                      &lt;/PayComponent&gt;
                    &lt;/PayComponents&gt;
                  &lt;/JobDetail&gt;
                &lt;/JobDetails&gt;
              &lt;/Level&gt;
            &lt;/Levels&gt;
          &lt;/JobMaster&gt;
        &lt;/JobMasters&gt;
        &lt;Reports&gt;
          &lt;Report Id="0" Name="standard" Type="Published" DatePublished="2015-04-06T14:29:52.7826312-07:00"&gt;
            &lt;JobDetails /&gt;
          &lt;/Report&gt;
        &lt;/Reports&gt;
      &lt;/Version&gt;
    &lt;/Versions&gt;
  &lt;/Survey&gt;
  </code></pre>
  
  <p>It is much larger, but this is the format. I want to dump these records into a denormalized table, containing everything from the Survey Name through Pay StatValue. I have tried many variations of the following script, but I can only correctly retrieve the Name of the Survey. Sometimes I can get the Survey Name and all StatValues concatenated as a string, all in one field like this..
  US CDB GI MMPS BaseAverageP10P25MedianP75P90, and it is not useful or expected. Anyway, here is the script I'm using</p>
  
  <pre><code>--------WORKS----------------------------
  DECLARE @myXML xml = (SELECT XMLData from XMLTest)
  SELECT 
  T.C.value('Name[1]', 'varchar(255)') as Surveys--,
  --T.C.value('JobCode[1]','varchar(255)') as JobCode
  FROM @myXML.nodes('(/Survey)') as T(C)
  
  -------------DOESN'T WORK-------------------------
  DECLARE @myXML xml = (SELECT XMLData from XMLTest)
  SELECT 
  T.C.value('Name[1]','varchar(255)') as VendorName
  FROM @myXML.nodes('(/Survey/Vendor)') as T(C)
  </code></pre>