CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
RAR
RAR

Reputation: 5

Assistance with XML tags

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!

Upvotes: 0

Views: 64

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

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>

Upvotes: 0

Charlieface
Charlieface

Reputation: 71144

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

Upvotes: 1

Related Questions