問題描述
假設我有這個設置:
-- tables
declare @main table (id int, name varchar(20))
declare @subA table (id int, mid int, name varchar(20))
declare @subA1 table (id int, subAid int, name varchar(20))
declare @subA2 table (id int, subAid int, name varchar(20))
declare @subB table (id int, mid int, name varchar(20))
-- sample data
insert @main values (1, 'A')
insert @main values (2, 'B')
insert @SubA values (1, 1, 'A')
insert @SubA values (2, 1, 'B')
insert @SubA values (3, 2, 'C')
insert @SubA1 values (1, 1, 'A')
insert @SubA2 values (1, 2, 'A')
insert @SubB values (1, 1, 'A')
insert @SubB values (2, 1, 'B')
insert @SubB values (3, 2, 'C')
-- results
select m.id, m.name, a.name, a1.name, a2.name, b.name
from @main m
left outer join @SubA a on m.id = a.mid
left outer join @SubA1 a1 on a.id = a1.subAid
left outer join @SubA2 a2 on a.id = a2.subAid
left outer join @SubB b on m.id = b.mid
返回:
1 A A A NULL A
1 A A A NULL B
1 A B NULL A A
1 A B NULL A B
2 B C NULL NULL C
如果我使用for xml auto"然后我得到:
If I use "for xml auto" then I get:
<m id="1" name="A">
<a name="A">
<a1 name="A">
<a2>
<b name="A" />
<b name="B" />
</a2>
</a1>
</a>
<a name="B">
<a1>
<a2 name="A">
<b name="A" />
<b name="B" />
</a2>
</a1>
</a>
</m>
<m id="2" name="B">
<a name="C">
<a1>
<a2>
<b name="C" />
</a2>
</a1>
</a>
</m>
然而，這不是我需要的.我想展示的是@main 是主表，它有兩個孩子:@subA 和@SubB.@SubA 反過來也有兩個孩子:@SubA1 和@SubA2，所以我想回來:
However, this isn't what I need. What I want to show is that @main is the main table which has two children: @subA and @SubB. @SubA in turn also has two children: @SubA1 and @SubA2, so I would like to get back:
<m id="1" name="A">
<a name="A">
<a1 name="A"></a1>
<a2></a2>
</a>
<a name="B">
<a1></a1>
<a2 name="A"></a2>
</a>
<b name="A" />
<b name="B" />
</m>
<m id="2" name="B">
<a name="C">
<a1></a1>
<a2></a2>
</a>
<b name="C" />
</m>
我很確定我將不得不使用for xml explicit"，但在我迄今為止嘗試過的所有嘗試中，我還沒有能夠獲得我需要的格式.
I'm pretty sure that I will have to use "for xml explicit", but out of all the attempts I have tried so far I haven't been able to get the format that I need.
誰能展示一個以所需格式返回數據的示例查詢?
Can anyone show an example query that will return the data in the required format?
謝謝，標記
推薦答案
你也可以重新編寫查詢來控制xml輸出，谷歌nested FOR XML QUERY.這是一個使用 FOR XML AUTO 的示例，您可能可以通過 FOR XML PATH 使用此技術獲得更好的控制.
You can also re-write query to control the xml output, Google nested FOR XML QUERY. Here is an example using FOR XML AUTO, you could probably get better control using this technique with FOR XML PATH.
-- tables
declare @main table (id int, name varchar(20))
declare @subA table (id int, mid int, name varchar(20))
declare @subA1 table (id int, subAid int, name varchar(20))
declare @subA2 table (id int, subAid int, name varchar(20))
declare @subB table (id int, mid int, name varchar(20))
-- sample data
insert @main values (1, 'm(1)')
insert @main values (2, 'm(2)')
insert @SubA values (1, 1, 'm(1)/a(1)')
insert @SubA values (2, 1, 'm(1)/a(2)')
insert @SubA values (3, 2, 'm(2)/a(3)')
insert @SubA1 values (1, 1, 'a(1)/a1(1)')
insert @SubA2 values (1, 1, 'a(1)/a2(1)')
insert @SubA2 values (2, 2, 'a(2)/a2(2)')
insert @SubB values (1, 1, 'm(1)/b(1)')
insert @SubB values (2, 1, 'm(1)/b(2)')
insert @SubB values (3, 2, 'm(2)/b(3)')
SELECT m.id
,m.name
,( SELECT [name]
,( SELECT [name]
FROM @subA1 AS a1
WHERE a1.subAid = a.id
FOR XML AUTO, TYPE
)
,( SELECT [name]
FROM @subA2 AS a2
WHERE a2.subAid = a.id
FOR XML AUTO, TYPE
)
FROM @SubA AS a
WHERE m.id = a.mid
FOR XML AUTO, TYPE
)
,( SELECT [name]
FROM @SubB AS b
WHERE m.id = b.mid
FOR XML AUTO, TYPE
)
FROM @main AS m
FOR XML AUTO
返回:
<m id="1" name="m(1)">
<a name="m(1)/a(1)">
<a1 name="a(1)/a1(1)" />
<a2 name="a(1)/a2(1)" />
</a>
<a name="m(1)/a(2)">
<a2 name="a(2)/a2(2)" />
</a>
<b name="m(1)/b(1)" />
<b name="m(1)/b(2)" />
</m>
<m id="2" name="m(2)">
<a name="m(2)/a(3)" />
<b name="m(2)/b(3)" />
</m>
這篇關于對于 XML 顯式的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！