XML Output

Question

Hi,

I must write a query from a table, with this XML output:

<DATASET_NAME>
<options>
<test/>
<transfer number="77"/>
</options>
<protokoll>
<dokinstanz>
<datetime name="DATE_NOW">20200518093438</datetime>
<application>
<applicationname name="SW">Software</applicationname>
<version name="SWV">2020</version>
</application>
</dokinstanz>
</protokoll>
<message>
<assessment>
<identification class="CLASSID">DATASET</identification>
</assessment>
<segment>
<person>
<identification class="TRANSFERID">12345</identification>
</person>
<datasegment>
<phrase>
<mm name="Column_A">
<value_A>55</value_A>
</mm>
<mm name="Column_B">
<value_B>88</value_B>
</mm>
</phrase>
</datasegment>
</segment>
</message>
</DATASET_NAME>

Is there a chance to write it in T-SQL? I have no ideas ...

Thanks.

Answer 1

Here is a conceptual example for you.
What we need from you is a DDL and sample data population section.

USE tempdb;
GO

-- DDL and sample data population, start
DROP TABLE IF EXISTS dbo.child;
DROP TABLE IF EXISTS dbo.parent;

CREATE TABLE dbo.parent (ID INT IDENTITY PRIMARY KEY, [State] CHAR(2));
CREATE TABLE dbo.child (
    ID INT IDENTITY PRIMARY KEY
    , ParentID INT NOT NULL FOREIGN KEY REFERENCES dbo.parent(ID)
    , City VARCHAR(30)
);

INSERT INTO dbo.parent ([State])
VALUES ('FL')
    , ('TX');

INSERT INTO dbo.child (ParentID, City)
VALUES (1, 'Miami')
    , (1, 'Fort Lauderdale')
    , (2, 'Austin')
    , (2, 'Dallas');
-- DDL and sample data population, end

SELECT * FROM dbo.parent;
SELECT * FROM dbo.child;

SELECT ID AS [@id]
    , [State] AS [@state]
    , (SELECT c.ID AS [@id]
            ,c.ParentID AS [@parentID]
            ,c.City AS [@city]
  FROM dbo.child AS c
  WHERE c.ParentID = p.ID
  FOR XML PATH ('city'), TYPE)
FROM dbo.parent AS p
FOR XML PATH('state'), ROOT('states');

Answer 2

Hi @Joerg 62 ,

Thank you so much for posting here.

As mentioned by other experts, it could be better for you to provide DDL and sample data.

Please also refer below simple example and check whether it is helpful.

CREATE TABLE #tempXML  
(  
    ItemNumber INT,  
    Price INT,  
    DateFrom DATE,  
    DateTo DATE  
)  
  
INSERT INTO #tempXML  
VALUES  
    (111, 3000, '2018-01-02', '2018-01-30'),  
    (111, 2500, '2018-01-31', '2018-11-22'),  
    (120, 4000, '2018-01-12', '2018-11-22')  
  
SELECT T.ItemNumber AS [@ID],  
       (SELECT T.ItemNumber,  
               (SELECT sq.Price AS [@value],  
                       (SELECT sq.DateFrom,  
                               sq.DateTo  
                        FOR XML PATH(''),TYPE)  
                FROM #tempXML sq  
                WHERE sq.ItemNumber = T.ItemNumber  
                FOR XML PATH('Price'),TYPE)  
        FOR XML PATH(''),TYPE)  
FROM #tempXML T  
GROUP BY ItemNumber  
FOR XML PATH ('Item');  

Output:

<Item ID="111"><ItemNumber>111</ItemNumber><Price value="3000"><DateFrom>2018-01-02</DateFrom><DateTo>2018-01-30</DateTo></Price><Price value="2500"><DateFrom>2018-01-31</DateFrom><DateTo>2018-11-22</DateTo></Price></Item><Item ID="120"><ItemNumber>120</ItemNumber><Price value="4000"><DateFrom>2018-01-12</DateFrom><DateTo>2018-11-22</DateTo></Price></Item>  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3