Ali-Wizard
New Member
How can the following statement best be modified so that I receive a list of Contact.Name with Address.Location (I'd like to use XPath and not subqueries/WHERE-conditions if possible)? I've tried all sorts of syntax at @@@Name but no luck 
- I want to reference the original node there.\[code\]DECLARE @data AS XMLSELECT @data = 'http://stackoverflow.com/questions/12624798/<Data> <Contact Name="John"/> <Contact Name="Bob"/> <Address ContactName="John" Location="JohnStreet1"/> <Address ContactName="John" Location="JohnStreet2"/> <Address ContactName="Bob" Location="BobStreet1"/> </Data> 'SELECT x.v.value('@Name','VARCHAR(255)') [Contact.Name], y.v.value('@Location','VARCHAR(255)') [Address.Location]FROM @data.nodes('/Data[1]/Contact') AS x(v)CROSS APPLY x.v.nodes('/Data[1]/Address[@ContactName=@@@Name]') AS y(v)ORDER BY x.v.value('@Name','VARCHAR(255)')\[/code\]PS. This won't work since the parameter needs to be a literal \[code\]x.v.nodes('/Data[1]/Address[@ContactName=' + x.v.value('@Name','VARCHAR(255)') + ']') AS y(v)\[/code\]
- I want to reference the original node there.\[code\]DECLARE @data AS XMLSELECT @data = 'http://stackoverflow.com/questions/12624798/<Data> <Contact Name="John"/> <Contact Name="Bob"/> <Address ContactName="John" Location="JohnStreet1"/> <Address ContactName="John" Location="JohnStreet2"/> <Address ContactName="Bob" Location="BobStreet1"/> </Data> 'SELECT x.v.value('@Name','VARCHAR(255)') [Contact.Name], y.v.value('@Location','VARCHAR(255)') [Address.Location]FROM @data.nodes('/Data[1]/Contact') AS x(v)CROSS APPLY x.v.nodes('/Data[1]/Address[@ContactName=@@@Name]') AS y(v)ORDER BY x.v.value('@Name','VARCHAR(255)')\[/code\]PS. This won't work since the parameter needs to be a literal \[code\]x.v.nodes('/Data[1]/Address[@ContactName=' + x.v.value('@Name','VARCHAR(255)') + ']') AS y(v)\[/code\]