CODEWITHSUNDEEP
sql-serverxmlt-sql
Brijesh
Brijesh

Reputation: 529

TSQL Parse XML document

I've an XML which i want to parse in SQL server. this XML has list of games and list of rule associated with each game. I want the output as

        GameID  RuleID  RuleType    IsSelected
        380      16       0          TRUE
        380      19       0          FALSE
        380      17       0          FALSE
        382      16       0          FALSE
        382      19       0          TRUE
        382      17       0          TRUE

I'm using below mentioned query to get the output but my game id is getting repeated for all the element of rule list associated with each game.

SELECT 
    Game.value('GameID[1]','INT') AS GameID
    ,Game.value('IsSelected[1]','bit') AS GameSelected
    ,Rule1.value('(./RuleID/text())[1]','Int') AS RuleID                             
FROM @x.nodes('GameWorld/GameList/Game') TEMPTABLE(Game) 
OUTER APPLY
Game.nodes('//RuleList/Rule') AS Rules(Rule1)

XML document is as mentioned below. This document contains the list of all games and each game has an rule list associated with it.

<GameWorld xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">  
  <GameList> 
    <Game> 
      <GameID>380</GameID>  
      <IsSelected>false</IsSelected>  
      <RuleList> 
        <Rule> 
          <RuleID>16</RuleID>  
          <RuleType>0</RuleType>  
          <IsSelected>true</IsSelected> 
        </Rule>  
        <Rule> 
          <RuleID>19</RuleID>  
          <RuleType>0</RuleType>  
          <IsSelected>false</IsSelected> 
        </Rule>  
        <Rule> 
          <RuleID>17</RuleID>  
          <RuleType>0</RuleType>  
          <IsSelected>false</IsSelected> 
        </Rule> 
      </RuleList> 
    </Game>  
    <Game> 
      <GameID>381</GameID>  
      <IsSelected>false</IsSelected>  
      <RuleList> 
        <Rule> 
          <RuleID>16</RuleID>  
          <RuleType>0</RuleType>  
          <IsSelected>true</IsSelected> 
        </Rule>  
        <Rule> 
          <RuleID>19</RuleID>  
          <RuleType>0</RuleType>  
          <IsSelected>false</IsSelected> 
        </Rule>  
        <Rule> 
          <RuleID>17</RuleID>  
          <RuleType>0</RuleType>  
          <IsSelected>false</IsSelected> 
        </Rule> 
      </RuleList> 
    </Game> 
  </GameList> 
</GameWorld>

Upvotes: 2

Views: 59

Answers (2)

Xin Zhang
Xin Zhang

Reputation: 161

SELECT 
    Game.value('GameID[1]','INT') AS GameID,
    Rule1.value('RuleID[1]','Int') AS RuleID,
    Rule1.value('RuleType[1]','Int') AS RuleType,
    Rule1.value('IsSelected[1]','varchar(5)') AS IsSelected
FROM @x.nodes('GameWorld/GameList/Game') TEMPTABLE(Game) 
OUTER APPLY
Game.nodes('//RuleList/Rule') AS Rules(Rule1)

Upvotes: 0

har07
har07

Reputation: 89285

The core problem is because you didn't use . before // in the context where you want the XPath/XQuery to be relative, i.e here Game.nodes('//RuleList/Rule'). Try this way instead :

SELECT 
    Game.value('GameID[1]','INT') AS GameID
    ,Rule1.value('RuleID[1]','Int') AS RuleID                             
    ,Rule1.value('RuleType[1]','Int') AS RuleType  
    ,Rule1.value('IsSelected[1]','bit') AS IsSelected
FROM @x.nodes('GameWorld/GameList/Game') TEMPTABLE(Game) 
OUTER APPLY
Game.nodes('./RuleList/Rule') AS Rules(Rule1)

sqlfiddle demo

output :

| GameID | RuleID | RuleType | IsSelected |
|--------|--------|----------|------------|
|    380 |     16 |        0 |       true |
|    380 |     19 |        0 |      false |
|    380 |     17 |        0 |      false |
|    381 |     16 |        0 |       true |
|    381 |     19 |        0 |      false |
|    381 |     17 |        0 |      false |

Upvotes: 1

Related Questions