問題描述
我不熟悉 SQL Server 中的FOR XML"功能.我使用的是 SQL Server 2012.
I'm new to the "FOR XML" feature in SQL Server. I am using SQL Server 2012.
我有兩個表格，Word 和 Word_Expansion.
I have two tables, Word and Word_Expansion.
示例數(shù)據(jù):
表[字]:
WordOID Word
------- ----
1 PIPE
2 WIRE
表 [Word_Expansion]:
table [Word_Expansion]:
WEOID fk_WordOID Word_Expansion
----- ---------- --------------
1 2 COAX
2 2 SPEAKER CABLE
3 1 CONDUIT
現(xiàn)在，我想生成類似于以下內(nèi)容的 XML:
Now, I would like to produce XML something like:
<expansion>
<sub>WIRE</sub>
<sub>SPEAKER CABLE</sub>
</expansion>
<expansion>
<sub>PIPE</sub>
<sub>CONDUIT</sub>
</expansion>
我在制作 XML FOR 語句方面進行了各種努力，但我似乎無法理解我需要做什么才能將這兩個表混合成正確的 XML 輸出.我將進一步深入研究 XML FOR 語法，但如果您有時間了解這一點...
I have come close with various efforts at crafting XML FOR statements, but I just can't seem to grasp what it is that I need to do to get these two tables mashed into the right XML output. I'll be digging further into XML FOR syntax, but if you have a moment and know this well...
有人對我應(yīng)該嘗試什么有任何指示嗎?
Does anyone have any pointers as to what I should try?
推薦答案
這應(yīng)該對你有用:
SQL:
SELECT Word Sub,
(
SELECT Word_Expansion AS Sub
FROM Word_Expansion WE
WHERE WE.fk_WordOID = W.WordOID
FOR XML PATH(''), type
)
FROM Word W
FOR XML PATH ('Expansion')
XML 輸出:
<Expansion>
<Sub>Pipe</Sub>
<Sub>CONDUIT</Sub>
</Expansion>
<Expansion>
<Sub>Wire</Sub>
<Sub>COAX</Sub>
<Sub>SPEAKER CABLE</Sub>
</Expansion>
雖然我不確定您為什么要將 Word.Word 歸類為Sub"?這不應(yīng)該是 Word_Expansion 的父級(應(yīng)該是 sub?)
Although i'm not sure why you want Word.Word to be classified as "Sub"? Shouldn't this instead be the parent of Word_Expansion (which should be sub?)
我發(fā)現(xiàn)這個鏈接在查看 FOR XML 和嵌套查詢時非常有用 嵌套 FOR XML
I found this link quite useful when looking into FOR XML and nested queries Nested FOR XML
這篇關(guān)于SQL Server“FOR XML"連接兩個表的查詢的輸出的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！