Change format of a Xml with FOR XML [SQL]

Question

I face an issue while trying to generate a XML Instance in SSMS, Im using 3 views to generate a xml file, here is the code I wrote:

    DECLARE @xmlDoc xml  
SET @xmlDoc = (
        SELECT *
        FROM   db.View1 AS v1
        INNER JOIN  db.View2 AS v2  ON v2.link = v1.link
        INNER JOIN db.View3 AS v3 ON v3.link = v1.link
        FOR XML AUTO)  
SELECT @xmlDoc

What I get is in this format:

<v1 field="data" ...>
    <v2 field="data" ...>
        <v3 field="data" .../>
    </v2>
</v1>

But I need to have v2 and v3 at the same level of imbrication, like this :

<v1 field="data" ...>
    <v2 field="data" .../>
    <v3 field="data" .../>
</v1>

If someone have any idea, It will help me a lot ! Thanks !

While asking a question, you need to provide a minimal, reproducible example. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT statements. (2) What you need to do, i.e. logic, and your code implementation of it. (3) Desired output based on the sample data. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Feb 28, 2020 at 12:55

Yitzhak Khabinsky · Accepted Answer · 2020-02-28 13:51:20Z

1

While we are waiting for your (1) DDL...

Please try the following as a conceptual example.

When you have a nested XML from different tables, you need to join them via WHERE clause. it simulates a correlated sub-query.

SQL

-- DDL and sample data population, start
DECLARE @tbl1 TABLE (ID INT PRIMARY KEY, [state] VARCHAR(20));
DECLARE @tbl2 TABLE (ID INT PRIMARY KEY, ParentID INT, [city] VARCHAR(20));
DECLARE @tbl3 TABLE (ID INT PRIMARY KEY, ParentID INT, [population] INT);

INSERT INTO @tbl1 (ID, state) 
VALUES ( 1, 'Florida');
INSERT INTO @tbl2 (ID, ParentID, city)
VALUES (10, 1, 'Miami'); 
INSERT INTO @tbl3 (ID, ParentID, population)
VALUES (5, 1, 470914);
-- DDL and sample data population, end

SELECT v1.state AS [field]
, (SELECT v2.city  AS [field]
    FROM @tbl2 AS v2
    WHERE v2.ParentID = v1.ID
    FOR XML AUTO, TYPE
    )
, (SELECT v3.population AS [field]
    FROM @tbl3 AS v3
    WHERE v3.ParentID = v1.ID
    FOR XML AUTO, TYPE
    )
FROM @tbl1 AS v1
FOR XML AUTO, TYPE;

Output

<v1 field="Florida">
  <v2 field="Miami" />
  <v3 field="470914" />
</v1>

edited Feb 28, 2020 at 13:51

answered Feb 28, 2020 at 13:31

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

You are taking the same route as I do. But, if I get this correctly, the OP did not want to use field as a fix name, but wants to rely on AUTO mode to name attributes after the columns. At least the original code uses a simple SELECT * without any table alias and relys on the AUTO mode to guess the structure magically... Anyway, +1 from my side as you were faster ;-)

Sleepless Over a year ago

Thanks for the reply, I'm sorry for the lack of informations, I thought It was enough, I'll make sure to give a sample with all data needed next time !

Gottfried Lesigang · Accepted Answer · 2020-02-28 14:06:24Z

0

In general I'd suggest to use FOR XML PATH(). This mode allows the highest control over the way the XML will look like. AUTO mode is - at least for - a bit to magic :-)

But you might try this:

Create a mockup to simulate your scenario (Please do this yourself in your next question):

DECLARE @t1 TABLE(ID1 INT IDENTITY,SomeValue VARCHAR(100));
INSERT INTO @t1 VALUES('test1'),('test2');

DECLARE @t2 TABLE(ID2 INT IDENTITY,linkT1 INT,SomeValue VARCHAR(100));
INSERT INTO @t2 VALUES(1,'test1.2.1'),(1,'test1.2.2'),(2,'test2.2.1');

DECLARE @t3 TABLE(ID3 INT IDENTITY,linkT1 INT,SomeValue VARCHAR(100));
INSERT INTO @t3 VALUES(1,'test1.3.1'),(1,'test1.3.2'),(2,'test2.3.1');

--Your query, slightly changed to correlated sub-queries:

DECLARE @xmlDoc xml  
SET @xmlDoc = (
        SELECT v1.*
              ,(SELECT * FROM @t2 AS vs2 WHERE vs2.linkT1=v1.ID1 FOR XML AUTO,TYPE)
              ,(SELECT * FROM @t3 AS vs3 WHERE vs3.linkT1=v1.ID1 FOR XML AUTO,TYPE)
        FROM  @t1 AS v1
        FOR XML AUTO)  
SELECT @xmlDoc;

The result

<v1 ID1="1" SomeValue="test1">
  <vs2 ID2="1" linkT1="1" SomeValue="test1.2.1" />
  <vs2 ID2="2" linkT1="1" SomeValue="test1.2.2" />
  <vs3 ID3="1" linkT1="1" SomeValue="test1.3.1" />
  <vs3 ID3="2" linkT1="1" SomeValue="test1.3.2" />
</v1>
<v1 ID1="2" SomeValue="test2">
  <vs2 ID2="3" linkT1="2" SomeValue="test2.2.1" />
  <vs3 ID3="3" linkT1="2" SomeValue="test2.3.1" />
</v1>

answered Feb 28, 2020 at 14:06

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

Sleepless Over a year ago

Hi Shnugo, and thanks for the reply, It seems to work perfectly, Again, I'm sorry for the lack of mockup, I didnt knew, and I'll make sure to do it right next time ! Thanks a lot !

Collectives™ on Stack Overflow

Change format of a Xml with FOR XML [SQL]

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related