时间戳

首页 > 解决方案 > SQL 表中的 XML - SQL Server

sql - SQL 表中的 XML - SQL Server

问题描述

我是 XML 新手，任务是从表中生成类似于下面提到的 xml：

<A>
<a1>1</a1>
<a2>2</a2>
<a3 type="xyz">abc</a3>
<B>
    <b1>11</b1>
    <b2 ID ="b2_product" name="clothing" >
        <c1>
            <d1 type = "xlr1">d11</d1>
            <d1 type = "xlr2">d12</d1>
        </c1>
    </b2>
</B>

用于此的 Sql 表是：

Table A:

a1   a2   a3_type    a3     b1      b2_ID       b2_name     xlr1    xlr2

1    2     xyz      abc     11   b2_product     clothing    d11     d12

如何编写 SQL 以仅以给定的 xml 格式从此表生成 XML。

标签： sqlsql-serverxmltsql

解决方案

你可能需要这样的东西：

DECLARE @tbl TABLE(
a1 int,a2 int, a3_type varchar(10),a3 varchar(10),b1 int,b2_ID varchar(10),b2_name varchar(10),xlr1 varchar(10),xlr2 varchar(10))
INSERT INTO @tbl VALUES
(1,2,'xyz','abc',11,'b2_product','clothing','d11','d12');

SELECT a1 
      ,a2
      ,a3_type AS [a3/@type]
      ,a3 
      ,b1 AS [B/b1]
      ,b2_ID AS [B/b2/@ID]
      ,b2_name AS [B/b2/@name]
      ,xlr1 AS [B/b2/c1/d1]
FROM @tbl 
FOR XML PATH('A')

结果

<A>
  <a1>1</a1>
  <a2>2</a2>
  <a3 type="xyz">abc</a3>
  <B>
    <b1>11</b1>
    <b2 ID="b2_product" name="clothing">
      <c1>
        <d1>d11</d1>
      </c1>
    </b2>
  </B>
</A>

更新

您编辑了您的问题：这是新查询

SELECT a1 
      ,a2
      ,a3_type AS [a3/@type]
      ,a3 
      ,b1 AS [B/b1]
      ,b2_ID AS [B/b2/@ID]
      ,b2_name AS [B/b2/@name]
      ,'xlr1' AS [B/b2/c1/d1/@type] 
      ,xlr1 AS [B/b2/c1/d1]
      ,'' AS [B/b2/c1]                --needed to start a new <d1>
      ,'xlr2' AS [B/b2/c1/d1/@type] 
      ,xlr2 AS [B/b2/c1/d1]

FROM @tbl 
FOR XML PATH('A')

推荐阅读