本文介紹了獲取嵌套的 XML 輸出 sql server的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
限時送ChatGPT賬號..
我的 sql server 數(shù)據(jù)庫的 xml 輸出有問題.我的桌子:
I have a problem with my xml output from sql server database. My table:
CREATE TABLE [dbo].[test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[firstname] [nvarchar](255) NULL,
[lastname] [nvarchar](255) NULL,
[city] [nvarchar](255) NULL,
[street] [nvarchar](255) NULL,
[streetno] [int] NULL
)
我想要輸出，其中 Address 嵌套在每個 Person 中，如下所示:
I want the output, where Address is nested inside each Person, like this:
<Root>
<Person id="1">
<firstname>Stefanie</firstname>
<lastname>Buckley</lastname>
<Address>
<city>Oklahoma</city>
<street> Cowley Road</street>
<streetno>34</streetno>
</Address>
</Person>
<Person id="2">
<firstname>Sandy</firstname>
<lastname>Mc Gee</lastname>
<Address>
<city>Montgomery</city>
<street> Hague Parkway</street>
<streetno>27</streetno>
</Address>
</Person>
</Root>
我嘗試過嵌套選擇，例如:
I've tried with nested select like:
select tbl1.id '@id', tbl1.firstname, tbl1.lastname,
(
select city,street,streetno from test_table as tbl2
where tbl2.id = tbl1.id
for xml path('Address')
)
from test_table as tbl1
for xml path('Person'), Root('Root')
但輸出如下:
<Root>
<Person id="1">
<firstname>Stefanie</firstname>
<lastname>Buckley</lastname><Address><city>Oklahoma</city><street> Cowley Road</street><streetno>34</streetno></Address></Person>
<Person id="2">
<firstname>Sandy</firstname>
<lastname>Mc Gee</lastname><Address><city>Anchorage</city><street> North Green Clarendon Road</street><streetno>29</streetno></Address></Person>
<Person id="3">
我做錯了什么?
推薦答案
你忘記了 ，輸入，你不需要 wxtra 從表中讀取.
You forgot , type and you don't need wxtra reading from table.
select tbl1.id '@id'
, tbl1.firstname
, tbl1.lastname
, (
select city
, street
, streetno
for xml path('Address'), type
)
from test_table as tbl1
for xml path('Person'), type, Root('Root')
這篇關于獲取嵌套的 XML 輸出 sql server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯(lián)系我們刪除處理，感謝您的支持！