T-sql查询在xml的所有节点中包含属性值

Hope you all are doing good. i am having a requirement that i want an sql query to include attributes value in all the nodes of the xml. in the below script ID value should come in all the node as attribute .Sample code and try for your reference.

希望你们都做得很好。我有一个要求，我希望一个SQL查询在xml的所有节点中包含属性值。在下面的脚本ID值中应该将所有节点作为属性。示例代码并尝试供您参考。

Sample Script:

示例脚本：

CREATE TABLE XMLGEN(ID INT,A VARCHAR(255),B  VARCHAR(255))

INSERT INTO XMLGEN  SELECT 1,'a1','b1' 
                    union all SELECT 2,'a2','b2'  
                    union all SELECT 3,'a3','b3'  
                    union all SELECT 4,'a4','b4'
                    union all SELECT 5,'a5','b5'

query which i tried:

我试过的查询：

select ID as '@Attribute', * from XMLGEN  FOR XML PATH('ImportData'), TYPE,root('root')

Output:

输出：

<root>
  <ImportData Attribute="1">
    <ID>1</ID>
    <A>a1</A>
    <B>b1</B>
  </ImportData>
  <ImportData Attribute="2">
    <ID>2</ID>
    <A>a2</A>
    <B>b2</B>
  </ImportData>
  <ImportData Attribute="3">
    <ID>3</ID>
    <A>a3</A>
    <B>b3</B>
  </ImportData>
  <ImportData Attribute="4">
    <ID>4</ID>
    <A>a4</A>
    <B>b4</B>
  </ImportData>
  <ImportData Attribute="5">
    <ID>5</ID>
    <A>a5</A>
    <B>b5</B>
  </ImportData>
</root>

Expected output:

预期产量：

<root>
  <ImportData>
    <ID Attribute="1">1</ID>
    <A Attribute="1">a1</A>
    <B Attribute="1">b1</B>
  </ImportData>
  <ImportData>
    <ID Attribute="2">2</ID>
    <A Attribute="2">a2</A>
    <B Attribute="2">b2</B>
  </ImportData>
  <ImportData>
    <ID Attribute="3">3</ID>
    <A Attribute="3">a3</A>
    <B Attribute="3">b3</B>
  </ImportData>
  <ImportData>
    <ID Attribute="4">4</ID>
    <A Attribute="4">a4</A>
    <B Attribute="4">b4</B>
  </ImportData>
  <ImportData>
    <ID Attribute="5">5</ID>
    <A Attribute="5">a5</A>
    <B Attribute="5">b5</B>
  </ImportData>
</root>

can anyone help to build the query?????

任何人都可以帮助建立查询?????

1 个解决方案

#1

select 
    ID as [ID/@Attribute]
    ,ID as [ID]
    ,ID as [A/@Attribute]
    ,A as [A]
    ,ID as [B/@Attribute]  
    ,B as [B]
from XMLGEN  FOR XML PATH('ImportData'), TYPE,root('root')