将XML分解为SQL Server

问题描述：

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

这是更大，但这是格式。我想将这些记录转储到非规范化表中，其中包含Survey Name到Pay StatValue中的所有内容。我已经尝试了以下脚本的许多变体，但我只能正确地检索调查的名称。有时我可以将Survey Name和所有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)

答

这里有一个想法给你。我试图为一些人撕碎数据，你可以为其他人做。

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分解为SQL Server

相关推荐