XML from SQL table - SQL Server

Question

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.

Gottfried Lesigang · Accepted Answer · 2018-06-07 07:51:44Z

2

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

edited Jun 7, 2018 at 7:51

answered Jun 7, 2018 at 7:45

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

KMittal Over a year ago

thanks a lot Sgnugo...it is what I wanted.... can you please share some resource or link where I can study a bit in more detail about this ...because usually Whenever i am searching it is not giving this kind of detailed material......

Gottfried Lesigang Over a year ago

@KMittal Thx! Best to start is the MS documentation on SELECT ... FOR XML PATH and the links there.

user7739833 Over a year ago

Is there any way to change name of xlr1 to x1 and xlr2 to x2....i.e. <d1 type = "x1">d11</d1> <d1 type = "x2">d12</d1>

Gottfried Lesigang Over a year ago

@JFI This data is a literal string 'xlr1', you can place any string there and you will find this as content of @type.

user7739833 Over a year ago

If we have an xsd, is this possible to generate similar xml from same table using sql query without making it this much complex .... like using Openrowset ?

|

Collectives™ on Stack Overflow

XML from SQL table - SQL Server

1 Answer 1

UPDATE

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related