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!
Advertisement
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);