SQL Server“FOR XML"连接两个表的查询的输出

2021-10-02 00:00:00 xml sql xsd sql-server

我不熟悉 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.

示例数据:

表[字]:

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

现在，我想生成类似于以下内容的 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...

有人对我应该尝试什么有任何指示吗?

Does anyone have any pointers as to what I should try?

推荐答案

这应该对你有用:

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"?这不应该是 Word_Expansion 的父级(应该是 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?)

我发现这个链接在查看 FOR XML 和嵌套查询时非常有用 嵌套 FOR XML

I found this link quite useful when looking into FOR XML and nested queries Nested FOR XML

相关文章