Сформировать xml с помощью FOR XML

Question

Я создал следующие таблицы:

Таблица 1 - Список застрахованных (ЗЛ) лиц с:
•   ФИО
•   Дата рождения
•   Пол

Таблица 2 - Страхование по терпрограмме обязательного медицинского страхования (ТП ОМС) с периодом страхования, состоит из:
•   Код страховой медицинской организации (СМО)  (1,2…)
•   Номер полиса ОМС (до 20 знаков)
•   Период страхования   (при последнем страховании конец периода принимает значение = пусто)

Таблица 3 - Справочник СМО:
•   Наименование СМО
•   Период действия лицензии СМО в ТП ОМС (по умолчанию конец периода принимает значение = пусто)

На каждого ЗЛ может быть несколько СМО с разными периодами страхования без пересечения

Но не могу сформировать следующую структуру:

<Z_LIST>
    <ZGL>
       <FILENAME>F1<FILENAME>
    </ZGL>
   <ZAP>
      <N_P>10001</N_P>
      <FAM>ИВАНОВ</FAM>
      <IM>ИВАН</IM>
      <OT>ИВАНОВИЧ</OT>
      <DR>2000-01-01 </DR >
      <W>1</W>
      <OMC>
           <SMO>2</SMO>
           <NAIM>СТРАХОВАЯ КОМПАНИЯ 2</NAIM >
           <POLIS >12345678 </POLIS >
           <D1>2000-02-01</D1>
           <D2>2010-01-01</D2>
      </OMC>
      <OMC>
           <SMO>1</SMO>
           <NAIM>СТРАХОВАЯ КОМПАНИЯ 1</NAIM >
           <POLIS >12345678 </POLIS >
           <D1>2010-02-01</D1>
      </OMC>
   <ZAP>
</Z_LIST>

МОЙ КОД:

DECLARE @FileName nvarchar(50) = 'fileName';

SELECT @FileName as 'FILENAME'
FOR XML PATH('ZGL'), TYPE, ELEMENTS

SELECT
Surname as N_P,
Name as FAM,
MiddleName as IM,
DateBirth as DR,
Gender as W
--SELECT CodeSMO AS 'OMS/SMO' FROM Table2 WHERE Table1_Id = t1.Id, 
FROM Table1 AS t1
ORDER BY Id
FOR XML PATH('ZAP'), TYPE, ELEMENTS, ROOT('Z_LIST')

Получаю 2 отдельных xml:

<ZGL>
  <FILENAME>fileName</FILENAME>
</ZGL>

<Z_LIST>
  <ZAP>
    <N_P>Иванов</N_P>
    <FAM>Иван</FAM>
    <IM>Иваныч</IM>
    <DR>2020-01-01</DR>
    <W>1</W>
  </ZAP>
  <ZAP>
    <N_P>Александрова</N_P>
    <FAM>Александра</FAM>
    <IM>Александровна</IM>
    <DR>2020-02-02</DR>
    <W>2</W>
  </ZAP>
</Z_LIST>

Answer 1

Сформируйте запрос для каждого фрагмента XML, потом объедините их в одном запросе.

Например, фрагмент 1:

SELECT 'Value' AS 'Element'
FOR XML PATH('Part1'), TYPE

фрагмент 2:

SELECT
    Col1 AS 'Element1',
    Col2 AS 'Element2'
FROM (VALUES
    (2, 'Col1.2', 'Col2.2'),
    (1, 'Col1.1', 'Col2.1')) T(Num, Col1, Col2)
ORDER BY Num
FOR XML PATH('Part2'), TYPE

Объединяем:

SELECT
    (-- фрагмент 1
        SELECT 'Value' AS 'Element'
        FOR XML PATH('Part1'), TYPE
    ),
    (-- фрагмент 2
        SELECT
            Col1 AS 'Element1',
            Col2 AS 'Element2'
        FROM (VALUES
            (2, 'Col1.2', 'Col2.2'),
            (1, 'Col1.1', 'Col2.1')) T(Num, Col1, Col2)
        ORDER BY Num
        FOR XML PATH('Part2'), TYPE
    )
FOR XML PATH('Root'), TYPE

И, кстати, запросы для вложенных фрагментов могут быть коррелированными по отношению к запросам для внешних фрагментов (что может потребоваться, когда есть связь между таблицами). Например:

SELECT
    T.N AS 'Value',
    (
        SELECT
            Sq AS 'Squared',
            Cb AS 'Cubed'
        FROM (VALUES
            (1, 1, 1),
            (2, 4, 8),
            (3, 9, 27)) T2(N, Sq, Cb)
        WHERE
            T2.N = T.N
        FOR XML PATH ('Derived'), TYPE
    )
FROM
    (VALUES (1), (2), (3), (4)) T(N)
FOR XML PATH ('Numbers'), ROOT('Root'), TYPE

(связали T и T2 по условию T2.N = T.N).

Answer 2

Попробуйте следующее решение.

И учитесь, когда задаете вопрос, как предоставлять (1) DDL и образец вставки данных, т.е. CREATE table(s) плюс операторы INSERT, в SQL.

SQL

-- DDL and sample data population, start
DECLARE @Insured TABLE (ID INT PRIMARY KEY, Surname NVARCHAR(50), Name NVARCHAR(50), MiddleName NVARCHAR(50), DOB DATE, Gender INT);
INSERT INTO @insured (ID, Surname, Name, MiddleName, DOB, Gender)
VALUES
(1, N'ИВАНОВ', N'ИВАН', N'ИВАНОВИЧ', '2000-01-01', 1),
(2, N'Александрова', N'Александра', N'Александровна', '2002-10-31', 2);

DECLARE @InsuranceCompany TABLE (CodeSMO INT PRIMARY KEY, NameSMO NVARCHAR(100), DateStartLicense DATE, DateEndLicense DATE);
INSERT INTO @InsuranceCompany (CodeSMO, NameSMO, DateStartLicense, DateEndLicense)
VALUES
(100, N'СТРАХОВАЯ КОМПАНИЯ Globus', '1980-01-01', '2020-12-31'),
(150, N'СТРАХОВАЯ КОМПАНИЯ Liberty', '1990-01-01', NULL);

DECLARE @Policy TABLE (CodeSMO INT, PolicyNumberOMG NVARCHAR(20), DateStart DATE, DateEnd DATE, InsuredID INT);
INSERT INTO @Policy (CodeSMO, PolicyNumberOMG, DateStart, DateEnd, InsuredID)
VALUES
(100, N'GL1000', '2000-02-01', '2010-01-01', 1),
(150, N'LI1207', '2010-02-01', NULL, 1),
(150, N'LI9876', '2015-05-01', NULL, 2);
-- DDL and sample data population, end

DECLARE @FileName nvarchar(50) = 'fileName';

SELECT @FileName AS [ZGL/FILENAME]
, (
    SELECT ID AS [N_P]
        , Surname as [FAM]
        , Name as [IM]
        , MiddleName as [OT]
        , DOB as [DR]
        , Gender as [W]
        , (
    SELECT p.CodeSMO AS [SMO], i.NameSMO AS [NAIM], p.PolicyNumberOMG AS [POLIS]
        , p.DateStart AS [D1], p.DateEnd AS [D2]
    FROM @Policy AS p INNER JOIN 
        @InsuranceCompany AS i ON i.CodeSMO = p.CodeSMO
    WHERE t.ID = p.InsuredID
    FOR XML PATH('OMC'), TYPE
    )
    FROM @Insured AS t
    ORDER BY Id
    FOR XML PATH('ZAP'), TYPE
)
FOR XML PATH(''), TYPE, ROOT('Z_LIST');

Результат

<Z_LIST>
  <ZGL>
    <FILENAME>fileName</FILENAME>
  </ZGL>
  <ZAP>
    <N_P>1</N_P>
    <FAM>ИВАНОВ</FAM>
    <IM>ИВАН</IM>
    <OT>ИВАНОВИЧ</OT>
    <DR>2000-01-01</DR>
    <W>1</W>
    <OMC>
      <SMO>100</SMO>
      <NAIM>СТРАХОВАЯ КОМПАНИЯ Globus</NAIM>
      <POLIS>GL1000</POLIS>
      <D1>2000-02-01</D1>
      <D2>2010-01-01</D2>
    </OMC>
    <OMC>
      <SMO>150</SMO>
      <NAIM>СТРАХОВАЯ КОМПАНИЯ Liberty</NAIM>
      <POLIS>LI1207</POLIS>
      <D1>2010-02-01</D1>
    </OMC>
  </ZAP>
  <ZAP>
    <N_P>2</N_P>
    <FAM>Александрова</FAM>
    <IM>Александра</IM>
    <OT>Александровна</OT>
    <DR>2002-10-31</DR>
    <W>2</W>
    <OMC>
      <SMO>150</SMO>
      <NAIM>СТРАХОВАЯ КОМПАНИЯ Liberty</NAIM>
      <POLIS>LI9876</POLIS>
      <D1>2015-05-01</D1>
    </OMC>
  </ZAP>
</Z_LIST>

БЛОГ НА HUSL

Сформировать xml с помощью FOR XML

Ответы (2 шт):