SQL Repeating Node

Question

SQL Repeating Node

Old Hand

Points: 374

March 12, 2023 at 10:30 pm

I'm attempting to utilise the SQL FOR XML clause to produce custom XML for usage in SSIS. I'm almost there. I can't seem to locate anything about the "type" option. For some reason, the word "type" brings me close. Help is greatly appreciated. This is the desired result.

<NLS CommitBlock="1" EnforceTagExistence="1">
  <LOAN loannumber="892127" acctrefno="145415" />
  <LOAN loannumber="892130" acctrefno="145418" />
  <LOAN loannumber="892131" acctrefno="145419" />
</NLS>

This is the result I'm receiving.

<NLS CommitBlock="1" EnforceTagExistence="1">
  <LOAN loannumber="892127" acctrefno="145415" />
</NLS>
<NLS CommitBlock="1" EnforceTagExistence="1">
  <LOAN loannumber="892130" acctrefno="145418" />
</NLS>
<NLS CommitBlock="1" EnforceTagExistence="1">
  <LOAN loannumber="892131" acctrefno="145419" />
</NLS>

This is my SQL.

SELECT  TOP (3) 
'1' AS 'CommitBlock',
'1' AS 'EnforceTagExistence',
(SELECT
vw_deferments.loan_number AS '@loannumber',
vw_deferments.acctrefno AS '@acctrefno'
FOR XML Path('LOAN'), type)
FROM            vw_deferments INNER JOIN
                loanacct ON vw_deferments.acctrefno = Iloanacct.acctrefno
WHERE (vw_deferments.Loan_Status IN ('PAYING', 'REPO REVIEW', 'REPO ASSIGN')) 
-- tons of irrelevant AND clauses are here
FOR XML RAW('NLS')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

SELECT '1' AS 'CommitBlock',
       '1' AS 'EnforceTagExistence',
       (SELECT vw_deferments.loan_number AS '@loannumber',
               vw_deferments.acctrefno AS '@acctrefno'
          FROM vw_deferments 
         INNER JOIN loanacct 
                 ON vw_deferments.acctrefno = loanacct.acctrefno
         WHERE (vw_deferments.Loan_Status IN ('PAYING', 'REPO REVIEW', 'REPO ASSIGN')) 
            -- tons of irrelevant AND clauses are here
           FOR XML Path('LOAN'), type
       )
   FOR XML Path('NLS')
;

Piyushbhatt Old Hand Points: 374 More actions · Answer 2

Piyushbhatt

Old Hand

Points: 374

March 14, 2023 at 1:51 pm

#4160592

Thank you good sir

drew.allen SSC Guru Points: 76944 More actions · Answer 3

The TYPE directive just says to return the results as XML rather than a string. TYPE Directive in FOR XML Queries

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

I don't use SSRS so I ask the following question in earnest... why do you need to use XML for this to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)