How to Get the following output using XPATH in SQL Sproc

Suppose the xml input is

<Tasks>
 <Task Name="Add2">
   <Dependency Name="S1"/>开发者_如何学编程
 </Task>
 <Task Name="Min2">
  <Dependency Name="Dev1"/>
  <Dependency Name="Extra"/>
  </Task>
<Tasks>

I want the outcome as

Add2   S1
Min2   Dev1
Min2   Extra

How to achieve this using Xpath in a Sql Sproc

Some of your previous questions has been about SQL Server so....

declare @xml xml = '
<Tasks>
 <Task Name="Add2">
   <Dependency Name="S1"/>
 </Task>
 <Task Name="Min2">
  <Dependency Name="Dev1"/>
  <Dependency Name="Extra"/>
  </Task>
</Tasks>'


select T1.N.value('@Name', 'varchar(max)') as TaskName,
       T2.N.value('@Name', 'varchar(max)') as DependencyName
from @xml.nodes('/Tasks/Task') as T1(N)
  cross apply T1.N.nodes('Dependency') as T2(N)

You technically need to path twice using xpath because you are accessing different nodes. I will give you the sql server way:

declare @xml_content xml = '<Tasks>
 <Task Name="Add2">
   <Dependency Name="S1"/>
 </Task>
 <Task Name="Min2">
  <Dependency Name="Dev1"/>
  <Dependency Name="Extra"/>
  </Task>
<Tasks>'

with roots as (
select x.value('@Name','varchar(max)') task_name,x.query('.') deps
from @xml_content.nodes('Tasks/Task') a(x)
)

select r.task_name,x.value('@Name','varchar(max)') dependency_name
from roots r
cross apply roots.deps.nodes('/Task/Dependency') a(x)