March 11, 2016 at 10:09 am
Hi All,
I am trying to create xml files in SQL 2008 R2 with 'for xml path'. I have not got experience with xml and I can't yet 'see' how the nests/elements work.
I can't find a clear explanation online on how to create xml files in SQL. I was hoping if someone knows of a tutorial I could follow so that I could give this a go on my own.
A little example of what I am trying to do - I don't know how to create the 'indentations' (nests?), if that makes sense?
The sql code is what I have started with, the issue is that I don't understand it and I would like to understand how it works.
I am hoping with a little help, the penny will drop.
Thank you in advance.
Michael
XML Output: (desired result)
<SubmissionId>1</SubmissionId>
<OriginalUniqueSubmissionid>1</OriginalUniqueSubmissionid>
<TestScenarioId>7-0</TestScenarioId>
<CorrectedInd>0</CorrectedInd>
<EmployerInformationGrp>
<BusinessName>
<BusinessNameLine1Txt>TestData</BusinessNameLine1Txt>
</BusinessName>
<BusinessNameControlTxt> </BusinessNameControlTxt>
<TINRequestTypeCd>TestData2</TINRequestTypeCd>
<EmployerEIN>00000001</EmployerEIN>
<MailingAddressGrp>
<USAddressGrp>
<AddressLine1Txt>109 Blah Cove</AddressLine1Txt>
<AddressLine2Txt> </AddressLine2Txt>
<CityNm>BlahCity</CityNm>
<USStateCd>TX</USStateCd>
<USZIPCd>00012</USZIPCd>
</USAddressGrp>
</MailingAddressGrp>
<ContactNameGrp>
<PersonFirstNm>Jon</PersonFirstNm>
<PersonLastNm>Doe</PersonLastNm>
</ContactNameGrp>
<ContactPhoneNum>5555555555</ContactPhoneNum>
</EmployerInformationGrp>
My code attempt:
SELECT
'' AS '@recordType',
'0' AS '@lineNum',
'1' AS 'SubmissionId',
'1' AS 'OriginalUniqueSubmissionId',
'3-0' AS 'TestScenarioId', --Just for Test
'0' AS 'CorrectedInd',
(select 'Selitestthree' AS 'BusinessName/BusinessNameLine1Txt',
' ' AS 'BusinessNameControlTxt',
' ' AS 'TINRequestTypeCd',
'00000000' AS 'EmployerEIN'
for xml PATH(''), type
) as 'EmployerInformationGrp',
'Jon' AS 'ContactNameGrp/PersonFirstNm',
' ' AS 'ContactNameGrp/PersonMiddleNm',
'Doe' AS 'ContactNameGrp/PersonLastNm',
'5555555555' AS 'ContactPhoneNum',
'blah rd' AS 'MailingAddressGrp/USAddressGrp/AddressLine1Txt',
' ' AS 'MailingAddressGrp/USAddressGrp/AddressLine2Txt',
'fdgdfg' AS 'MailingAddressGrp/USAddressGrp/CityNm',
'AA' AS 'MailingAddressGrp/USAddressGrp/USStateCd',
'00214' AS 'MailingAddressGrp/USAddressGrp/USZIPCd',
'3' AS 'Form1095CAttachedCnt',
'1' as 'AuthoritativeTransmit',
'455' AS 'Form1095CFiledCnt',
'1' AS 'AleMemberGroup',
'1' as 'Sect4980HTransRel',
' ' AS 'JuratSignaturePIN',
' ' AS 'PersonTitleTxt',
' 'AS 'SignatureDt'
for xml path('Form1094BUpstreamDetail'),ROOT ('Form109495BTransmittalUpstream')
March 11, 2016 at 11:44 am
You usually use subqueries to create complex structures, although you can also use CROSS APPLY.
SELECT
1 AS SubmissionID,
1 AS OriginalUniqueSubmissionID,
'7-0' AS TestScenarioID,
0 AS CorrectedInd,
( SELECT
( SELECT 'TestData' AS BusinessNameLine1Txt FOR XML PATH('BusinessName'), TYPE ),
' ' AS BusinessNameControlTxt,
'TestData2' AS TINRequestTypeCd,
'0000001' AS EmployerEIN,
(
SELECT
'109 Blah Cove' AS AddressLine1Txt,
'' AS AddressLine2Txt,
'BlahCity' AS CityNm,
'TX' AS USStateCd,
'00012' AS USZIPCd
FOR XML PATH('USAddressGrp'), ROOT('MailingAddressGrp'), TYPE
) ,
( SELECT 'Jon' AS PersonFirstNm, 'Doe' AS PersonLastNm FOR XML PATH('ContactNameGrp'), TYPE ),
5555555555 AS ContactPhoneNum
FOR XML PATH('EmployerInformationGrp'), TYPE
)
FOR XML PATH('Form1094BUpstreamDetail'),ROOT ('Form109495BTransmittalUpstream')
The TYPE keyword ensures that the results are returned as XML rather than NVARCHAR.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 11, 2016 at 12:02 pm
Drew,
Thank you so much, it's already much more clearer with your help - the formatted\layout syntax really helps. This is an excellent starting point.
Appreciate your time.
Michael
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply