Complex stored procedure for XML generation

Naomi 7,366 Reputation points
2023-05-30T21:41:11.94+00:00

Hi everybody,

I have a complex stored procedure that generates an XML file. The portion I'm concerned is currently looks like this:

<item>

  <CodedSpecialty>...</CodedSpecialty>

  <Relationship>CNN</Relationship>

   <Location>...

           <CoverageArea>...</CoverageArea>

     </Location>

    <HighPerformingProvider>...</HighPerformingProvider>

<item>

This is portion of the code generating above:


	SELECT
											LTRIM(RTRIM(caresiteserv3.codeofservicedescription)) CodedSpecialty
											, 'CCN' Relationship
											, (
												  SELECT
													  LTRIM(RTRIM(caresite.sitename)) Name
													  , (
															SELECT TOP(1)
																cl.address_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal														
														)
													  , LTRIM(RTRIM(caresite.sitetype)) SiteType
													  , LTRIM(RTRIM(caresite.siteid)) SiteId
													  , LTRIM(RTRIM(caresite.ishandicapaccessible)) IsHandicapAccessible
													  , (
															SELECT TOP(1)
																cl.phone_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal
															
														)
													  , (
															SELECT TOP(1)
																cl.fax_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal
														)
													  , (
															SELECT TOP(1)
																cl.email_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal
														)

													  --/** Org Coverage Area **/
													  , (
															SELECT
																cl.CoverageArea_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal
																AND cl.AreaType = csl.AreaType
																AND cl.AreaCode = csl.AreaCode
														)


													  /** ORG Center of Excellence***/
													  , (
															SELECT TOP (1)
																cl.CenterOfExcellence_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal
																
																
														)
													  /** Place of Service Codes **/
													  , (
															SELECT TOP (1)
																cl.PlaceOfServiceCodes_xml
															FROM
																@caresiteid_list cl
															WHERE
																cl.caresiteidinternal = caresiteserv2.caresiteidinternal
														
														)
												  FROM
													  consume.vw_PROV_PMF_CARESITE_SPLIT_REGION caresite
													  INNER JOIN consume_tbl.PROV_PMF_CARESITESERVICE_SPLIT caresiteserv2
														  ON caresiteserv2.caresiteidinternal = caresite.caresiteidinternal
															 AND caresiteserv3.codeofservicedescription = caresiteserv2.codeofservicedescription
															 AND caresiteserv3.caresiteidinternal = caresiteserv2.caresiteidinternal
													  INNER JOIN @caresiteid_list csl
														  ON csl.caresiteidinternal = caresiteserv2.caresiteidinternal
												  WHERE
													  caresite.region_nbr = @inp_region_nbr
												  GROUP BY
													  caresite.sitename
													  , caresite.sitetype
													  , caresite.siteid
													  , caresite.ishandicapaccessible
													  , caresiteserv2.caresiteidinternal
													  , csl.AreaType
													  , csl.AreaCode
												  ORDER BY
													  caresite.sitename
												  FOR XML PATH('Location'), TYPE
											  )
											/** ORG High Performing Provider **/
											, (
												  SELECT TOP (1)
													  cl.HighPerformingProvider_xml
												  FROM
													  @caresiteid_list cl
												  WHERE
													  cl.caresiteidinternal = caresiteserv3.caresiteidinternal
													  		
											  )
										FROM
											consume_tbl.PROV_PMF_CARESITESERVICE_SPLIT caresiteserv3
											INNER JOIN consume.vw_PROV_PMF_CARESITE_SPLIT_REGION careSite3
												ON careSite3.caresiteidinternal = caresiteserv3.caresiteidinternal
											--INNER JOIN consume.vw_PROV_PMF_ADDRESS Addres
											--	ON Addres.caresiteidinternal = caresiteserv3.caresiteidinternal
											--	   AND Addres.adresstypecd IN ('01', 'M')
											INNER JOIN (SELECT DISTINCT caresiteIdInternal, cntTaxonomy FROM @caresiteid_list) csl3
												ON csl3.caresiteidinternal = caresiteserv3.caresiteidinternal
										--LEFT JOIN consume_tbl.PROV_PMF_COVERAGE_AREA ca
										--	ON csl.externalProviderNumber = ca.externalprovidernumber
										WHERE
											careSite3.region_nbr = @inp_region_nbr
											AND
											(
												caresiteserv3.codeofservicedescription NOT IN
												(
													'193200000X' -- multi-specialty
													, '193400000X' -- single specialty
												)
												AND csl3.cntTaxonomy > 1
												OR csl3.cntTaxonomy = 1
											)
										GROUP BY
											caresiteserv3.codeofservicedescription
											, caresiteserv3.caresiteidinternal
											
										ORDER BY
											caresiteserv3.codeofservicedescription
											, caresiteserv3.caresiteidinternal
											
										FOR XML PATH('Item'), TYPE

My problem is that I have multiple coverage areas. For each coverage area I want to explode the item node. My current code explodes the location node inside the single item node. The above code is only small portion of the very complex procedure. I cannot provide DDL for the tables involved or the data. I'm wondering if it somehow possible to modify the above code so the explosion happens on the item node and not the inner location node. I don't see a way :(

I need the code to work in SQL 2016.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,264 questions
{count} votes