asked
How to add 2 roots For XML in SQL Server?
I want to add another root ` ` before and after original root ` ` . Please have a look at the SQL and XML output. The SQL already have sub XML. Any suggestion? --SQL CODE-- SELECT [CLIENT_NO] AS ClientId ,'true' AS ConsentToProvideDetails ,'true' AS ConsentedForFutureContacts ,SUBSTRING([CLIENT_NAME], 1, CHARINDEX(' ', [CLIENT_NAME], -1)) AS GivenName ,[LAST_NAME] AS FamilyName ,'false' AS IsUsingPsuedonym ,[BIRTH_DATE] AS BirthDate ,'false' AS IsBirthDateAnEstimate ,[dbo].[GC2DEX]('Gender', SEX) AS GenderCode ,[BIRTH_COUNTRY_AUNZ_CODE] AS CountryOfBirthCode ,[LANGUAGE_AUNZ_CODE] AS LanguageSpokenAtHomeCode ,[dbo].[GC2DEX]('AboriginalOrTorresStraitIslanderOrigin', ETHNICITY) AS AboriginalOrTorresStraitIslanderOriginCode ,IIF (CUSTOM_03 is null, 'false', 'true') AS HasDisabilities ,IIF (custom_03 is null, NULL, (select [dbo].[GC2DEX]('Disability', [CUSTOM_03]) FROM [GC_Cache].[dbo].[CRI] AS C2 WHERE C2.CLIENT_NO = C1.CLIENT_NO for xml path ('DisabilityCode'), type )) AS Disabilities ,[dbo].[GC2DEX]('AccommodationType', [ASSESS_ACCOMODATION]) AS AccommodationTypeCode ,[dbo].[GC2DEX]('DVACardStatus', VETERAN_STATUS_CODE) AS DVACardStatusCode ,(select HS_CLIENT_ADDR1 AS AddressLine1, HS_CLIENT_ADDR2 AS AddressLine2, HS_CLIENT_CITY AS Suburb, HS_CLIENT_STATE AS StateCode, HS_CLIENT_ZIP_CODE AS Postcode FROM [GC_Cache].[dbo].[CRI] AS C2 WHERE C2.CLIENT_NO = C1.CLIENT_NO for xml path('ResidentialAddress'), type ) FROM [GC_Cache].[dbo].[CRI] AS C1 WHERE [BIRTH_COUNTRY_AUNZ_CODE] is not null FOR XML PATH ('Client'), ROOT ('Clients') /****------XML OUTPUT-------****/ 9307 true true HEATHER PARSONS false 1999-09-09 false FEMALE 1101 1201 NOTSTATED false NOTSTATED NODVA 94 First Ave Melton VIC 3338 ................................. .................................
The quickest and dirtiest way I can thing of doing this is to subquery the original query, get the result as varchar, cast to XML, and add the 'extra' root something like select ( cast(( ... your original query but without the ROOT directive .... FOR XML PATH ('Client') ) as xml) ) for xml path('Clients'), root ('DEXField')
Thanks for your idea Kev. I got an easier idea from your code. I just added a blank select statement. I did the following and it worked as well. select '' ,(SELECT TOP 10 [CONTRACT_NO] AS CaseId ,[EXTERNAL_NO] AS OutletActivityId ,0 AS TotalNumberOfUnidentifiedClients ,(SELECT [CLIENT_NO] AS ClientId FROM [GC_Cache].[dbo].[HS_CONTRACT] AS C2 WHERE C2.CONTRACT_NO = C1.CONTRACT_NO AND STATUS IN ('A', 'C') FOR XML PATH('CaseClient'), ROOT ('CaseClients'), Type) -- ,[GROUP_A] FROM [GC_Cache].[dbo].[HS_CONTRACT] AS C1 WHERE STATUS IN ('A', 'C') AND ISNUMERIC (EXTERNAL_NO) = 1 FOR XML PATH ('Case'), ROOT ('Cases'), type) FOR XML PATH ('DEXFieldUpload')
18 People are following this question.
