如何查询嵌套XML节点的行并返回结果？

假设有以下XML数据：

<People> <Person> <Name>John</Name> <Age>30</Age> <Address> <Street>123 Main St</Street> <City>Seattle</City> <State>WA</State> </Address> </Person> <Person> <Name>Jane</Name> <Age>25</Age> <Address> <Street>456 Oak St</Street> <City>Portland</City> <State>OR</State> </Address> </Person> </People>

要查询每个人的姓名、街道和城市，可以使用以下代码：

SELECT People.Person.value('(Name)[1]', 'varchar(50)') as Name, People.Person.value('(Address/Street)[1]', 'varchar(50)') as Street, People.Person.value('(Address/City)[1]', 'varchar(50)') as City FROM @xmlData.nodes('/People/Person') as People(Person)

使用nodes()函数把XML数据拆分为多行，然后使用value()函数读取每个人的姓名、街道和城市。结果如下：

Name Street City John 123 Main St Seattle Jane 456 Oak St Portland