XML分解到SQL Server(XML Shredding into SQL Server)

我有以下格式的XML文档。

<?xml version="1.0"?>
<Survey xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Name>US CDB GI MMPS </Name>
  <Vendor Name="TWDS" />
  <Versions>
    <Version Name="All Functions Incumbent Weighted Accouting Value" Year="2014">
      <JobMasters>
        <JobMaster JobCode="AAS000-ALL-M1" JobTitle="AAS000 Administrative Services Generalist/Multidiscipline">
          <Levels>
            <Level LevelCode="M1">
              <JobDetails>
                <JobDetail Scope="Global Revenue">
                  <PayComponents>
                    <PayComponent Id="1" Name="Base" CompanyCount="16" IncumbentCount="281" IsPercent="false">
                      <Type>Base</Type>
                      <Pays>
                        <Pay Id="100" Stat="Average" Value="65.6">
                          <StatValue>Average</StatValue>
                        </Pay>
                        <Pay Id="10" Stat="P10" Value="41">
                          <StatValue>P10</StatValue>
                        </Pay>
                        <Pay Id="25" Stat="P25" Value="51.6">
                          <StatValue>P25</StatValue>
                        </Pay>
                        <Pay Id="101" Stat="Median" Value="66.3">
                          <StatValue>Median</StatValue>
                        </Pay>
                        <Pay Id="75" Stat="P75" Value="74.2">
                          <StatValue>P75</StatValue>
                        </Pay>
                        <Pay Id="90" Stat="P90" Value="89.5">
                          <StatValue>P90</StatValue>
                        </Pay>
                      </Pays>
                    </PayComponent>
                  </PayComponents>
                </JobDetail>
              </JobDetails>
            </Level>
          </Levels>
        </JobMaster>
      </JobMasters>
      <Reports>
        <Report Id="0" Name="standard" Type="Published" DatePublished="2015-04-06T14:29:52.7826312-07:00">
          <JobDetails />
        </Report>
      </Reports>
    </Version>
  </Versions>
</Survey>

这是更大的，但这是格式。 我想这些记录转储到非规范化表，通过收费StatValue包含一切从调查名称。 我曾尝试下面的脚本的许多变化，但我只能正确地检索调查的名称。 有时候，我可以得到调查名称，然后连接起来作为一个字符串的所有StatValues，都在这样的..美国CDB GI MMPS BaseAverageP10P25MedianP75P90一个领域，它是没有用的或预期的。 总之，这里是我使用的脚本

--------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)

Answer 1:

下面是你的想法。 我试图撕碎了一些数据，你能为别人做。

DECLARE @myXML xml = '<?xml version="1.0"?>
<Survey xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Name>US CDB GI MMPS </Name>
  <Vendor Name="TWDS" />
 <Versions>
<Version Name="All Functions Incumbent Weighted Accouting Value" Year="2014">
  <JobMasters>
    <JobMaster JobCode="AAS000-ALL-M1" JobTitle="AAS000 Administrative Services Generalist/Multidiscipline">
      <Levels>
        <Level LevelCode="M1">
          <JobDetails>
            <JobDetail Scope="Global Revenue">
              <PayComponents>
                <PayComponent Id="1" Name="Base" CompanyCount="16" IncumbentCount="281" IsPercent="false">
                  <Type>Base</Type>
                  <Pays>
                    <Pay Id="100" Stat="Average" Value="65.6">
                      <StatValue>Average</StatValue>
                    </Pay>
                    <Pay Id="10" Stat="P10" Value="41">
                      <StatValue>P10</StatValue>
                    </Pay>
                    <Pay Id="25" Stat="P25" Value="51.6">
                      <StatValue>P25</StatValue>
                    </Pay>
                    <Pay Id="101" Stat="Median" Value="66.3">
                      <StatValue>Median</StatValue>
                    </Pay>
                    <Pay Id="75" Stat="P75" Value="74.2">
                      <StatValue>P75</StatValue>
                    </Pay>
                    <Pay Id="90" Stat="P90" Value="89.5">
                      <StatValue>P90</StatValue>
                    </Pay>
                  </Pays>
                </PayComponent>
              </PayComponents>
            </JobDetail>
          </JobDetails>
        </Level>
      </Levels>
    </JobMaster>
  </JobMasters>
  <Reports>
    <Report Id="0" Name="standard" Type="Published" DatePublished="2015-04-06T14:29:52.7826312-07:00">
      <JobDetails />
    </Report>
  </Reports>
</Version>
     </Versions>
     </Survey>'

   DECLARE @docH INT;
    EXEC [sys].[sp_xml_preparedocument] @docH OUTPUT, @myXML;

SELECT *
FROM OPENXML(@docH,    '/Survey/Versions/Version/JobMasters/JobMaster/Levels/Level/JobDetails/JobDetail/PayComponents/PayComponent/Pays/Pay', 3)
 WITH (Stat nvarchar(50) '@Stat',
  Value DECIMAL '@Value',
  Id INT    '@Id',
  PayComponentName NVARCHAR(50) '../../@Name',
  PayComponentId int '../../@Id',
  JobDetailScope NVARCHAR(20) '../../../../@Scope'
  )

 EXEC [sys].[sp_xml_removedocument] @docH

文章来源: XML Shredding into SQL Server