Assistance with XML tags-CodePudding

I'm working on a SQL query (MS SQL) that will output as XML. I need assistance with some tags that have the same tag name, <CustomInteger>. What my output should look like is this:

   <CustomIntegers>
      <CustomInteger>
        <FieldID>2701</FieldID>
        <Value>15</Value>
      </CustomInteger>
      <CustomInteger>
        <FieldID>2704</FieldID>
        <Value>28</Value>
      </CustomInteger>
      <CustomInteger>
        <FieldID>2705</FieldID>
        <Value>28</Value>
      </CustomInteger>
    </CustomIntegers>

But what I'm currently getting is this:

<CustomIntegers>
  <CustomInteger>
    <FieldID>2701</FieldID>
    <Value>15</Value>
    <FieldID>2704</FieldID>
    <Value>28</Value>
    <FieldID>2705</FieldID>
    <Value>28</Value>
  </CustomInteger>
</CustomIntegers>

I need the <FieldID> and <Value> tags within its own <CustomInteger> tag.

--Creating table

CREATE TABLE [dbo].[Student](
    [TermCode] [varchar](5),
    [StudentID] [varchar](9),
    [RegisteredHours] [decimal](7, 4) NULL,
    [CreditsAttempted] [decimal](7, 4) NULL,
    [CreditsEarned] [decimal](7, 4) NULL
) ON [PRIMARY]

GO

--Inserting records

Insert into Student (TermCode, StudentID, RegisteredHours, CreditsAttempted, CreditsEarned)
values ('20211', '123456789', '9.0000', '6.0000', '6.0000')

Insert into Student (TermCode, StudentID, RegisteredHours, CreditsAttempted, CreditsEarned)
values ('20211', '234567890', '15.0000', '28.0000', '28.0000')

Insert into Student (TermCode, StudentID, RegisteredHours, CreditsAttempted, CreditsEarned)
values ('20211', '345678901', '12.0000', '30.0000', '27.0000')

Insert into Student (TermCode, StudentID, RegisteredHours, CreditsAttempted, CreditsEarned)
values ('20211', '456789012', '10.0000', '30.0000', '30.0000')

Insert into Student (TermCode, StudentID, RegisteredHours, CreditsAttempted, CreditsEarned)
values ('20211', '567890123', '12.0000', '32.0000', '29.0000')

--The code I have so far:

select 
'2701' as [CustomInteger/FieldID],
cast(RegisteredHours as int) AS [CustomInteger/Value],
'2704' as [CustomInteger/FieldID],
cast(CreditsAttempted as int) AS [CustomInteger/Value],
'2705' as [CustomInteger/FieldID],
cast(CreditsEarned as int) AS [CustomInteger/Value]
from Student
for xml path('CustomIntegers'), type, elements

Any assistance would be greatly appreciated!

CodePudding user response：

You can unpivot the values and turn them into XML, inside a correlated subquery (per row)

SELECT
   (
       SELECT FieldID, Value
       FROM (VALUES
         ('2701',cast(RegisteredHours as int)),
         ('2704',cast(CreditsAttempted as int)),
         ('2705',cast(CreditsEarned as int))
       ) v(FieldID, Value)
       FOR XML PATH('CustomInteger'), TYPE
   ) AS CustomIntegers
FROM Student
FOR XML PATH(''), TYPE;

db<>fiddle

CodePudding user response：

Please try the following solution.

It seems that your output XML should be composed of the data from one single row.

SQL #1

-- DDL and sample data population, start
DECLARE @student TABLE (
    [TermCode] [varchar](5),
    [StudentID] [varchar](9),
    [RegisteredHours] [decimal](7, 4) NULL,
    [CreditsAttempted] [decimal](7, 4) NULL,
    [CreditsEarned] [decimal](7, 4) NULL
);
INSERT INTO @student (TermCode, StudentID, RegisteredHours, CreditsAttempted, CreditsEarned) VALUES 
('20211', '123456789', '9.0000', '6.0000', '6.0000'),
('20211', '234567890', '15.0000', '28.0000', '28.0000'),
('20211', '345678901', '12.0000', '30.0000', '27.0000'),
('20211', '456789012', '10.0000', '30.0000', '30.0000'),
('20211', '567890123', '12.0000', '32.0000', '29.0000');
-- DDL and sample data population, end

SELECT (
    SELECT TRY_CAST(RegisteredHours AS INT) AS RegisteredHours
        , TRY_CAST(CreditsAttempted AS INT) AS CreditsAttempted
        , TRY_CAST(CreditsEarned AS INT) AS CreditsEarned
    FROM @student
    WHERE StudentID = '234567890'
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<CustomIntegers>
{
    for $x in /root/r/*
    return <CustomInteger>
        <FieldID>
        {
            if (local-name($x)="RegisteredHours") then "2701"
            else if (local-name($x)="CreditsAttempted") then "2704"
            else if (local-name($x)="CreditsEarned") then "2705"
            else ()
        }
        </FieldID>
        <Value>{data($x)}</Value>
    </CustomInteger>
}
</CustomIntegers>');

SQL #2

SELECT '2701' as [CustomInteger/FieldID]
    , TRY_CAST(RegisteredHours as int) AS [CustomInteger/Value], NULL
    , '2704' as [CustomInteger/FieldID]
    , TRY_CAST(CreditsAttempted as int) AS [CustomInteger/Value], NULL
    , '2705' as [CustomInteger/FieldID]
    , TRY_CAST(CreditsEarned as int) AS [CustomInteger/Value]
FROM @Student
--WHERE StudentID = '234567890'
FOR XML PATH('CustomIntegers'), TYPE, ROOT('root');

Output

<CustomIntegers>
  <CustomInteger>
    <FieldID>2701</FieldID>
    <Value>15</Value>
  </CustomInteger>
  <CustomInteger>
    <FieldID>2704</FieldID>
    <Value>28</Value>
  </CustomInteger>
  <CustomInteger>
    <FieldID>2705</FieldID>
    <Value>28</Value>
  </CustomInteger>
</CustomIntegers>