CODEWITHSUNDEEP
sql-serverxmlt-sql
Sleepless
Sleepless

Reputation: 15

Change format of a Xml with FOR XML [SQL]

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 !

Upvotes: 0

Views: 72

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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>

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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>

Upvotes: 1

Related Questions