Equivalent of the following query without FOR XML PATH
-
Hi, What is the equivalent of the following query without the FOR XML PATH, when removed the FOR XML PATH from the query it is giving error, can anybody please help with this regards, thanks in advance buddies.
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi, What is the equivalent of the following query without the FOR XML PATH, when removed the FOR XML PATH from the query it is giving error, can anybody please help with this regards, thanks in advance buddies.
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
I am having a query on the Production Server, I am getting an xml out of it. For some reason there is a problem in the data that I am getting, some data is missing, but I can't easily compare and see it on the xml file what am I missing and why. It would be easier for me if I put the same Data into Table then I can easily compare what am I missing. Or is there anyway that I can read the Data from xml file and put it into a table? I am trying but I have to create table before it seems, but I want to just import into Table like we do from excel files. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
I am having a query on the Production Server, I am getting an xml out of it. For some reason there is a problem in the data that I am getting, some data is missing, but I can't easily compare and see it on the xml file what am I missing and why. It would be easier for me if I put the same Data into Table then I can easily compare what am I missing. Or is there anyway that I can read the Data from xml file and put it into a table? I am trying but I have to create table before it seems, but I want to just import into Table like we do from excel files. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
CHill60 wrote:
You might get a better response with some sample data and expected results
My comment still stands.
Indian143 wrote:
when removed the FOR XML PATH from the query it is giving error
Are you are expecting us to guess what that error might be? There are plenty of people who would like to help you solve your problem, but you're not giving us much to go on. We do this in our spare time so expecting us to wade through the SQL to try and create some tables to match it so we can see what is being produced so we can work out how to replace the FOR XML and get appropriate results ... would take more time than most of us have available. See Some guidelines for posting questions in the forums[^]
-
CHill60 wrote:
You might get a better response with some sample data and expected results
My comment still stands.
Indian143 wrote:
when removed the FOR XML PATH from the query it is giving error
Are you are expecting us to guess what that error might be? There are plenty of people who would like to help you solve your problem, but you're not giving us much to go on. We do this in our spare time so expecting us to wade through the SQL to try and create some tables to match it so we can see what is being produced so we can work out how to replace the FOR XML and get appropriate results ... would take more time than most of us have available. See Some guidelines for posting questions in the forums[^]
I can understand sorry my friend, the real data of it is PHI and if I have to enter sample data also its difficult, what I was thinking is, maybe somebody has knowledge of For Xml strongly so that they can convert this query into normal sql query, I didn't think that everybody needs sample data, I am extremely sorry my friend. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
I can understand by can you please help me how can I create dummy values for the views vw_ServicesXML5010, vw_ServiceFacilityXML5010, as those views are not fetching any records, the xml is not generating those elements, is it possible to help me in that regards. For example if I don't have any records for the View vw_ServicesXML5010 for that where condition I want to have empty value for the elements Procedure_Code, Modifier1, Modifier2, Modifier3, Modifier4, From_Date, To_Date, Revenue_Code, Place_Of_Service, Taxonomy_Code Same thing applies to the View: vw_ServiceFacilityXML5010, if it doesn't have any records then at least one record set with empty values for the Service_Facility_NPI, County_Owned_Indicator. Can you please help me buddy, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
