SQL Server 2008/2012不解析XML Soap

问题描述：

哦，今天是什么。我试图解析SQL Server中的某些XML，并且之前已经完成了一些非常庞大的文件，但对于这些文件，我遇到了最大的问题。我实际上是通过XML做了递归解析，这样我就可以正确识别所有节点，所以我知道我并不疯狂。SQL Server 2008/2012不解析XML Soap

我的代码是这样的。

DECLARE @XML XMl 

SELECT @XML = xmldata from testing where testingID = 3 

select T.c.value('Legs[1]', 'varchar(100)') as Legs 
/*t.c.value('(PoolId)[1]', 'varchar(100)') as [Pool], 
t.c.value('(./Model/@id)[1]', 'int') ModelID, 
t.c.value('(./Note)[1]', 'varchar(100)') Note, 
t.c.value('(./Part)[1]', 'varchar(15)') Part*/ 
from @xml.nodes('/Envelope/Body/GetEventDetailResponse/EventsResponse/EventDetail/Races/Race/Pools/Pool') as T(c)

我留下了一些旧的代码，我知道这些旧的XML文件的工作没有问题，但评论说出来。

的XML是这个

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"> 
<s:Body> 
    <GetEventDetailResponse xmlns="http://schema.unitedtote.com/ToteLink/2008/06/Program"> 
    <EventsResponse xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> 
     <RunId xmlns="http://schema.unitedtote.com/ToteLink/2008/06/Common">2613</RunId> 
     <GroupId xmlns="http://schema.unitedtote.com/ToteLink/2008/06/Common">TH1</GroupId> 
     <Source xmlns="http://schema.unitedtote.com/ToteLink/2008/06/Common"> 
      <SystemId>TSG</SystemId> 
      <SourceId>TH1TST</SourceId> 
     </Source> 
     <EventDetail> 
      <Races> 
       <Race> 
       <Pools> 
        <Pool> 
         <PoolId>WIN</PoolId> 
         <PoolNumber>1</PoolNumber> 
         <PoolName>Win</PoolName> 
         <PoolRaces>1</PoolRaces> 
         <Legs>1</Legs> 
         <Quick>true</Quick> 
         <Box>true</Box> 
         <SingleBetMinimum>2</SingleBetMinimum> 
         <MultipleBetMinimum>2</MultipleBetMinimum> 
         <WagerMinimum>2</WagerMinimum> 
         <Maximum>10000</Maximum> 
         <Probables>true</Probables> 
         <WBMaximum>10000</WBMaximum> 
         <Straight>true</Straight> 
        </Pool> 
        <Pool> 
         <PoolId>PLC</PoolId> 
         <PoolNumber>2</PoolNumber> 
         <PoolName>Place</PoolName> 
         <PoolRaces>1</PoolRaces> 
         <Legs>1</Legs> 
         <Quick>true</Quick> 
         <Box>true</Box> 
         <SingleBetMinimum>2</SingleBetMinimum> 
         <MultipleBetMinimum>2</MultipleBetMinimum> 
         <WagerMinimum>2</WagerMinimum> 
         <Maximum>10000</Maximum> 
         <WBMaximum>10000</WBMaximum> 
         <Straight>true</Straight> 
        </Pool> 
        <Pool> 
         <PoolId>EX</PoolId> 
         <PoolNumber>4</PoolNumber> 
         <PoolName>Exacta</PoolName> 
         <PoolRaces>1</PoolRaces> 
         <Legs>2</Legs> 
         <Quick>true</Quick> 
         <Wheel>true</Wheel> 
         <Box>true</Box> 
         <PowerBox>true</PowerBox> 
         <KeyWheel>true</KeyWheel> 
         <SingleBetMinimum>2</SingleBetMinimum> 
         <MultipleBetMinimum>1</MultipleBetMinimum> 
         <WagerMinimum>2</WagerMinimum> 
         <Maximum>10000</Maximum> 
         <WBMaximum>10000</WBMaximum> 
         <Straight>true</Straight> 
        </Pool> 
</Race> 
      </Races> 
      <EventId>XVD</EventId> 
      <RunId>2613</RunId> 
      <EventName>Vaal</EventName> 
      <TrackId>XV</TrackId> 
      <TrackName>Vaal</TrackName> 
      <EventTime>2015-04-02T16:30:00</EventTime> 
      <EventInfo>2 Apr 15</EventInfo> 
      <EventType>Current</EventType> 
      <TrackType>Thoroughbred</TrackType> 
      <CurrencyId>USD</CurrencyId> 
      <EventStatus>Open</EventStatus> 
      <RaceList>1-8</RaceList> 
      <BreakTo>0.1</BreakTo> 
     </EventDetail> 
    </EventsResponse> 
    </GetEventDetailResponse>

这将是一个比很多在脚本级别写出一个分析例程更有效。

答

您必须指定命名空间，就像这样：

;with xmlnamespaces(
    'http://schemas.xmlsoap.org/soap/envelope/' as s, 
    default 'http://schema.unitedtote.com/ToteLink/2008/06/Program' 
) 
select 
    T.c.value('Legs[1]', 'varchar(100)') as Legs 
from @xml.nodes('s:Envelope/s:Body/GetEventDetailResponse/EventsResponse/EventDetail/Races/Race/Pools/Pool') as T(c)

sql fiddle demo

SQL Server 2008/2012不解析XML Soap

相关推荐