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')
March 13, 2023 at 10:41 am
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')
;
March 14, 2023 at 1:51 pm
Thank you good sir
March 14, 2023 at 3:19 pm
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
March 14, 2023 at 5:28 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy