Filter SQL Server XML by Attribute and return all matching attribute value for elements-CodePudding

I have the following XML:

<Games>
  <Game>
    <Place City="LAS" State="NV" />
    <Place City="ATL" State="GA" />
    <Store Store_ID="12" Price_ID="166" Description="Demons v3" />
    <Store Store_ID="12" Price_ID="171" Description="Race v4" />
    <Store Store_ID="12" Price_ID="162" Description="Doom" />
    <Store Store_ID="12" Price_ID="575" Description="Pac-man" />
    <Store Store_ID="13" Price_ID="167" Description="Demons v3" />
    <Store Store_ID="13" Price_ID="170" Description="Race v4" />
    <Store Store_ID="13" Price_ID="163" Description="Doom" />
    <Store Store_ID="13" Price_ID="576" Description="Pac-man" />
  </Game>
  <Game>
    <Place City="NYC" State="NY" />
    <Place City="ATL" State="GA" />
    <Store Store_ID="12" Price_ID="166" Description="Grand Thft Auto V" />
    <Store Store_ID="12" Price_ID="171" Description="Red Dead Redemption" />
    <Store Store_ID="12" Price_ID="162" Description="Super Mario Brothers 3" />
    <Store Store_ID="12" Price_ID="575" Description="Game Controller" />
    <Store Store_ID="13" Price_ID="167" Description="Grand Thft Auto V" />
    <Store Store_ID="13" Price_ID="170" Description="Red Dead Redemption" />
    <Store Store_ID="13" Price_ID="163" Description="Super Mario Brothers 3" />
    <Store Store_ID="13" Price_ID="576" Description="Game Controller" />
  </Game>
  <Game>
    <Place City="MIA" State="FL" />
    <Place City="LAS" State="NV" />
    <Store Store_ID="12" Price_ID="349" Description="Demons v3" />
    <Store Store_ID="12" Price_ID="350" Description="Race v4" />
    <Store Store_ID="12" Price_ID="346" Description="Doom" />
    <Store Store_ID="12" Price_ID="579" Description="Pac-man" />
    <Store Store_ID="13" Price_ID="167" Description="Grand Theft Auto V" />
    <Store Store_ID="13" Price_ID="170" Description="Red Dead Redemption" />
    <Store Store_ID="13" Price_ID="163" Description="Super Mario Brothers 3" />
    <Store Store_ID="13" Price_ID="576" Description="Game Controller" />
  </Game>
</Games>

I am having difficulty filtering and returning the desired information using SQL. What I'm looking to accomplish is to filter using the <Place> element's attributes: <city> and <state> to return a complete list of <Store> attributes IDs:

For example:

SELECT G.S.Store_ID AS Store
,      G.S.Price_ID AS Price
FROM @xml.nodes('') G(S)
WHERE City="NYC" AND State="NY"

should return:

Store #    Price #
========   ========
12         166
12         171
12         162
12         575 
13         167                   
13         170                   
13         163                                                
13         576

CodePudding user response：

Please try the following solution.

SQL

DECLARE @xml XML =
N'<Games>
    <Game>
        <Place City="LAS" State="NV"/>
        <Place City="ATL" State="GA"/>
        <Store Store_ID="12" Price_ID="166" Description="Demons v3"/>
        <Store Store_ID="12" Price_ID="171" Description="Race v4"/>
        <Store Store_ID="12" Price_ID="162" Description="Doom"/>
        <Store Store_ID="12" Price_ID="575" Description="Pac-man"/>
        <Store Store_ID="13" Price_ID="167" Description="Demons v3"/>
        <Store Store_ID="13" Price_ID="170" Description="Race v4"/>
        <Store Store_ID="13" Price_ID="163" Description="Doom"/>
        <Store Store_ID="13" Price_ID="576" Description="Pac-man"/>
    </Game>
    <Game>
        <Place City="NYC" State="NY"/>
        <Place City="ATL" State="GA"/>
        <Store Store_ID="12" Price_ID="166" Description="Grand Thft Auto V"/>
        <Store Store_ID="12" Price_ID="171" Description="Red Dead Redemption"/>
        <Store Store_ID="12" Price_ID="162" Description="Super Mario Brothers 3"/>
        <Store Store_ID="12" Price_ID="575" Description="Game Controller"/>
        <Store Store_ID="13" Price_ID="167" Description="Grand Thft Auto V"/>
        <Store Store_ID="13" Price_ID="170" Description="Red Dead Redemption"/>
        <Store Store_ID="13" Price_ID="163" Description="Super Mario Brothers 3"/>
        <Store Store_ID="13" Price_ID="576" Description="Game Controller"/>
    </Game>
    <Game>
        <Place City="MIA" State="FL"/>
        <Place City="LAS" State="NV"/>
        <Store Store_ID="12" Price_ID="349" Description="Demons v3"/>
        <Store Store_ID="12" Price_ID="350" Description="Race v4"/>
        <Store Store_ID="12" Price_ID="346" Description="Doom"/>
        <Store Store_ID="12" Price_ID="579" Description="Pac-man"/>
        <Store Store_ID="13" Price_ID="167" Description="Grand Theft Auto V"/>
        <Store Store_ID="13" Price_ID="170" Description="Red Dead Redemption"/>
        <Store Store_ID="13" Price_ID="163" Description="Super Mario Brothers 3"/>
        <Store Store_ID="13" Price_ID="576" Description="Game Controller"/>
    </Game>
</Games>';

SELECT c.value('@Store_ID', 'INT') AS Store_ID
    , c.value('@Price_ID', 'INT') AS Price_ID
FROM @xml.nodes('/Games/Game[Place[@City="NYC" and @State="NY"]]/Store') AS t(c)

Output

 ---------- ---------- 
| Store_ID | Price_ID |
 ---------- ---------- 
|       12 |      166 |
|       12 |      171 |
|       12 |      162 |
|       12 |      575 |
|       13 |      167 |
|       13 |      170 |
|       13 |      163 |
|       13 |      576 |
 ---------- ----------