How to use SQL .nodes AND .exist

I asked a previous question but did not get any responses. This is to try again and make it simpler. For the life of me I can’t figure out how to get this working the way I need. Let’s say I have some XML:

<aliases>
  <alias>
    <templateId @root="1" />
    <aliasName>Pramod Singh</aliasName>
  </alias>
  <alias>
    <templateId @root="2" />
    <aliasName>Bijoy Bora</aliasName>
  </alias>
  <alias>
    <templateId @root="1" />
    <aliasName>Testy McTestFace</aliasName>
  </alias>
</aliases>

In the example above, I want the aliasName but only if the parent alias tag has a templateId with a root value of 1. So my expected result is:

Pramod Singh

Testy McTestFace

I have tried using cross apply @XML.nodes(/aliases/alias) but then can’t use the .exist on said nodes. I have also tried using @XML.query but it does not return multiple instances. There has to be an easy way to accomplish this, but I just can’t figure it out.

Keep in mind this is an extremely simplified XML, I am just trying to illustrate my problem. My actual XML files I am retrieving many values and the size is between 1-3 MB per file.

Thank you!

Answer

Please try the following SQL:

DECLARE @xml XML = N'<aliases>
    <alias>
        <templateId root="1"/>
        <aliasName>Pramod Singh</aliasName>
    </alias>
    <alias>
        <templateId root="2"/>
        <aliasName>Bijoy Bora</aliasName>
    </alias>
    <alias>
        <templateId root="1"/>
        <aliasName>Testy McTestFace</aliasName>
    </alias>
</aliases>';

SELECT c.value('(templateId/@root)[1]','INT') AS ARoot
    , c.value('(aliasName/text())[1]','VARCHAR(30)') AS AliasName
FROM @xml.nodes('/aliases/alias[templateId/@root=("1","2")]') AS t(c);