CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
KMittal
KMittal

Reputation: 602

XML from SQL table - SQL Server

I am new to XML , The task is to generate xml similar to below mentioned from table :

<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 for this is :

Table A:

a1   a2   a3_type    a3     b1      b2_ID       b2_name     xlr1    xlr2

1    2     xyz      abc     11   b2_product     clothing    d11     d12

How to write SQL to generate XML from this table in given format of xml only.

Upvotes: 1

Views: 58

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67281

You probably need something like this:

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')

The result

<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>

UPDATE

you edited your question: Here's the new query

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')

Upvotes: 2

Related Questions