将SQL表转换为XML，每行2个节点 [英] Convert SQL table to XML with 2 node for each row

问题描述

我的MS SQL表具有以下数据

My MS SQL table has following data

    ID    CONTENT     FLAG       TEXT
    ----------------------------------
    1     content1    T          text1
    2     content2    F          text2
    3     content3    T          text2

并且我想以以下格式从该表中选择值，而没有任何根节点.

and i want to select values from this table in below format without any root node.

<ADDRESS ID="1" FLAG="T">
<FORM CONTENT="content1" TEXT="text1"/>
</ADDRESS>

<ADDRESS ID="2" FLAG="F">
<FORM CONTENT="content2" TEXT="text2"/>
</ADDRESS>

<ADDRESS ID="3" FLAG="T">
<FORM CONTENT="content3" TEXT="text3"/>
</ADDRESS>

推荐答案

这已经有一段时间了，但是-以防万一您仍然对解决方案感兴趣-这将是查询:

This is quite a while ago, but - just in case you're still interested in a solution - this would be the query:

 DECLARE @tbl TABLE(ID INT,CONTENT VARCHAR(100),FLAG VARCHAR(1),TEXT VARCHAR(100));
 INSERT INTO @tbl VALUES
 (1,'content1','T','text1')
,(2,'content2','F','text2')
,(3,'content3','T','text2');

SELECT tbl.ID AS [@ID]
      ,tbl.FLAG AS [@FLAG]
      ,tbl.CONTENT AS [Form/@CONTENT]
      ,tbl.TEXT AS [Form/@Text]
FROM @tbl AS tbl
FOR XML PATH('ADDRESS') /*,ROOT('Root')*/ --add Root if you need it

结果

<ADDRESS ID="1" FLAG="T">
  <Form CONTENT="content1" Text="text1" />
</ADDRESS>
<ADDRESS ID="2" FLAG="F">
  <Form CONTENT="content2" Text="text2" />
</ADDRESS>
<ADDRESS ID="3" FLAG="T">
  <Form CONTENT="content3" Text="text2" />
</ADDRESS>

这篇关于将SQL表转换为XML，每行2个节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！