CODEWITHSUNDEEP
sql-serverxmlsql-server-2008
user1263981
user1263981

Reputation: 3147

How to group one to many data in one XML node - SQL Server

There is a one to many relation between tables, one inspector can have many documents and I like to group them by inspector id and show them in one xml <row> node.

What I have tried so far is here;

SQL:

select 
    d.InspectorID  "@InspectorID", 
    d.DocID  "Qualification/DocID", 
    d.FileSize "Qualification/FileSize", 
    q.Name "Qualification/Name", 
    d.EndDate "Qualification/EndDate", 
    d.DateCreated "Qualification/DateCreated"
from 
    InspectorDocs d
LEFT JOIN 
    DocumentType dt on dt.DocumentTypeID = d.DocType
LEFT JOIN 
    QualificationType q on q.QualificationTypeID =  d.QualificationTypeID
WHERE  
    d.InspectorID = 6390 
FOR XML PATH

XML output:

<row InspectorID="6390">
    <Qualification>
        <DocID>23107</DocID>
        <FileSize>248724</FileSize>
        <Name>AWS CWI</Name>
        <EndDate>2016-12-01T00:00:00</EndDate>
        <DateCreated>2014-07-23T21:30:00</DateCreated>
    </Qualification>
</row>
<row InspectorID="6390">
    <Qualification>
        <DocID>23108</DocID>
        <FileSize>524934</FileSize>
        <Name>Other</Name>
        <EndDate>1900-12-12T00:00:00</EndDate>
        <DateCreated>2014-07-23T21:31:00</DateCreated>
    </Qualification>
</row>

How can I achieve this desired output:

<row InspectorID="6390">
    <Qualification>
          <DocID>23107</DocID>
          <FileSize>248724</FileSize>
          <Name>AWS CWI</Name>
          <EndDate>2016-12-01T00:00:00</EndDate>
          <DateCreated>2014-07-23T21:30:00</DateCreated>
    </Qualification>
    <Qualification>
          <DocID>23108</DocID>
          <FileSize>524934</FileSize>
          <Name>Other</Name>
          <EndDate>1900-12-12T00:00:00</EndDate>
          <DateCreated>2014-07-23T21:31:00</DateCreated>
    </Qualification> 
</row>

Upvotes: 0

Views: 48

Answers (2)

TT.
TT.

Reputation: 16145

The following query gives the result you aim for. It uses a single table, but you can adapt it to the tables + joining you need.

DECLARE @t TABLE(
    InspectorID INT,
    DocID INT,
    FileSize INT,
    Name VARCHAR(256),
    EndDate DATETIME,
    DateCreated DATETIME
);

INSERT INTO @t
    (InspectorID,DocID,FileSize,Name,EndDate,DateCreated)
VALUES
    (6390,23107,248724,'AWS CWI','2016-12-01T00:00:00','2014-07-23T21:30:00'),
    (6390,23108,524934,'Other','1900-12-12T00:00:00','2014-07-23T21:30:00');

DECLARE @SelectedID INT=6390;

SELECT
    @SelectedID "@InspectorID",
    (
        SELECT
            t.DocID "Qualification/DocID", 
            t.FileSize "Qualification/FileSize", 
            t.Name "Qualification/Name", 
            t.EndDate "Qualification/EndDate", 
            t.DateCreated "Qualification/DateCreated"
        FROM
            @t AS t
        WHERE  
            t.InspectorID=@SelectedID
        FOR XML PATH(''), TYPE
    )
FOR XML PATH, TYPE;

Result:

<row InspectorID="6390">
  <Qualification>
    <DocID>23107</DocID>
    <FileSize>248724</FileSize>
    <Name>AWS CWI</Name>
    <EndDate>2016-12-01T00:00:00</EndDate>
    <DateCreated>2014-07-23T21:30:00</DateCreated>
  </Qualification>
  <Qualification>
    <DocID>23108</DocID>
    <FileSize>524934</FileSize>
    <Name>Other</Name>
    <EndDate>1900-12-12T00:00:00</EndDate>
    <DateCreated>2014-07-23T21:30:00</DateCreated>
  </Qualification>
</row>

Upvotes: 1

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Try this:

SELECT  d1.InspectorID "@InspectorID"
      , ( SELECT    d.DocID "Qualification/DocID"
                  , d.FileSize "Qualification/FileSize"
                  , q.Name "Qualification/Name"
                  , d.EndDate "Qualification/EndDate"
                  , d.DateCreated "Qualification/DateCreated"
          FROM      InspectorDocs d
                    LEFT JOIN DocumentType dt ON dt.DocumentTypeID = d.DocType
                    LEFT JOIN QualificationType q ON q.QualificationTypeID = d.QualificationTypeID
          WHERE     d.InspectorID = d1.InspectorID
        FOR
          XML PATH
            , TYPE
        )
FROM    ( SELECT DISTINCT
                    InspectorID
          FROM      InspectorDocs
        ) d1
WHERE   d1.InspectorID = 6390
FOR     XML PATH

Upvotes: 1

Related Questions