sql-serverxmlt-sqlxquery-sql

All steps required to produce a XML file from a SQL Server table

enter image description hereenter image description hereenter image description hereenter image description hereI need to produce output in specified XML format, thought it would be easy in 2021, tried first to convert it into XSD using online services and use it with my SQL (using CREATE XML SCHEMA COLLECTION Schema1 AS @Schema1);but this didn't work, I suspect that there is no easy way to generate XML using XSD, still need to work with your SELECT, please correct me if I'm wrong. My example below is not final. I'm using SQL Server 2017 and have only SSMS as my tool. (no .NET available).

picxxxxxx

Here is my sample XML. enter image description here

rybaenter image description here

<?xml version="1.0" encoding="UTF-8"?>
<Users>
    <User Active="0"  gender="Male" DOB="1965-02-12" mi="X" lName="John" fName="Dorx">
        <CInfo ResArea="Montigo"/>
        <Demographic race="Asian" HE="No"/>
        <RA>
            <Memb StartDD="2004-06-11" eStatus="Active" StatusAsOf="2004-05-12" UserID="XHD15"/>
        </RA>
    </User>
    <User Active="0"  gender="Male" DOB="1977-04-14"  mi="X" lName="Mario" fName="Ma">
        <CInfo ResArea="Blanco"/>
        <Demographic race="White" HE="Yes"/>
        <RA>
            <Memb StartDD="2004-02-22" eStatus="Active" StatusAsOf="2004-03-26" UserID="MMX12"/>
            <Memb StartDD="2004-12-22" eStatus="Active" StatusAsOf="2004-05-26" UserID="MMX12"/>
        </RA>
    </User>
</Users>

And here is my test code and SQL (which is not final yet, I still struggling with formatting all tags, and I probably need to do subquery and grouping to list 2 Memb under same User).

/*
SELECT * INTO #t FROM (
SELECT 'XHD15' UserID, '1965-02-12' DOB, 'John' lName,  'Dorx' fName, 'x' mi,  'Montigo' ResArea,  '2004-06-11' StartDD, 'Active' eStatus, '2004-05-12' StatusAsOf, 'Asian' race, 'No' HE  union
SELECT 'MMX12' UserID, '1977-04-14' DOB, 'Mario' lName,  'Ma' fName, 'x' mi, 'Blanco' ResArea,  '2004-02-22' StartDD, 'Active' eStatus, '2004-03-26' StatusAsOf, 'White' race, 'No' HE  union
SELECT 'MMX12' UserID, '1977-04-14' DOB, 'Mario' lName,  'Ma' fName, 'x' mi, 'Blanco' ResArea,  '2004-12-22' StartDD, 'Active' eStatus, '2004-12-26' StatusAsOf, 'White' race, 'No' HE  )x  
*/
  SELECT  Lname AS [@Lname], Fname [@Fname], mi [@mi],  DOB [@DOB],
         MAX(ResArea) AS [CInfo/@ResArea],
         MAX(race) AS [Demographic/@race],    MAX(HE) AS [Demographic/@HE] 
       , MAX(StartDD) AS [RA/Memb/@StartDD],  MAX(eStatus) AS [RA/Memb/@eStatus],   MAX(StatusAsOf)  AS [RA/Memb/@StatusAsOf]
       -- Need subquery for multi StartDD
 FROM #t AS [User]    
 GROUP BY Lname, Fname , mi ,  DOB 
      FOR XML PATH ('User'),  ROOT ('Users')-- , ELEMENTS

enter image description here bravo

enter image description here

enter image description here

Solution

  • Please try the following solution.

    The SQL below is using two aliases: p(arent) and c(hild).

    When generating nested XML, parent and child data sets are joined via WHERE clause.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (
    ID INT IDENTITY PRIMARY KEY, 
    UserID CHAR(5), DOB DATE, 
    lName VARCHAR(20), 
    fName VARCHAR(20), 
    mi VARCHAR(20),
    ResArea VARCHAR(30),
    StartDD DATE,
    eStatus VARCHAR(10),
    StatusAsOf DATE,
    race  VARCHAR(20),
    HE VARCHAR(5)
);
INSERT INTO @tbl VALUES
('XHD15', '1965-02-12', 'John' ,  'Dorx', 'x', 'Montigo', '2004-06-11', 'Active', '2004-05-12', 'Asian', 'No'),
('MMX12', '1977-04-14', 'Mario',  'Ma'  , 'x', 'Blanco' , '2004-02-22', 'Active', '2004-03-26', 'White', 'No'),
('MMX12', '1977-04-14', 'Mario',  'Ma'  , 'x', 'Blanco' , '2004-12-22', 'Active', '2004-12-26', 'White', 'No');
 -- DDL and sample data population, end

SELECT Lname AS [@Lname], Fname [@Fname], mi [@mi],  DOB [@DOB]
    , ResArea AS [CInfo/@ResArea]
    , race AS [Demographic/@race], HE AS [Demographic/@HE]
    , (SELECT StartDD AS [@StartDD]
            , eStatus AS [@eStatus]
            , StatusAsOf AS [@StatusAsOf]
            , UserID AS [@UserID]
        FROM @tbl AS c
        WHERE p.UserID = c.UserID
        FOR XML PATH('Memb'), TYPE, ROOT('RA')
    )
FROM @tbl AS p
GROUP BY p.UserID, p.Lname, p.Fname, p.mi, p.DOB, p.ResArea, p.race, p.HE
FOR XML PATH ('User'), TYPE, ROOT ('Users');

    Output XML

    <Users>
  <User Lname="Mario" Fname="Ma" mi="x" DOB="1977-04-14">
    <CInfo ResArea="Blanco" />
    <Demographic race="White" HE="No" />
    <RA>
      <Memb StartDD="2004-02-22" eStatus="Active" StatusAsOf="2004-03-26" UserID="MMX12" />
      <Memb StartDD="2004-12-22" eStatus="Active" StatusAsOf="2004-12-26" UserID="MMX12" />
    </RA>
  </User>
  <User Lname="John" Fname="Dorx" mi="x" DOB="1965-02-12">
    <CInfo ResArea="Montigo" />
    <Demographic race="Asian" HE="No" />
    <RA>
      <Memb StartDD="2004-06-11" eStatus="Active" StatusAsOf="2004-05-12" UserID="XHD15" />
    </RA>
  </User>
</Users>