|
Hi!
I have sent a reply but it is currently under review but that was to another person. Do I need to reply to your message or will you be able to see it?
|
|
|
|
|
That is strange... I wonder why this is not under review? How long does a review take?
|
|
|
|
|
Hi,
I am getting following error message when trying to restore a Backup of Sql Server 2012 Database on a Sql Server 2008 Server, the error message says following:
This error arises if you attempt to restore a database from a backup created on a later version of SQL Server to an earlier version of SQL Server. You can only restore to the same or a later version of SQL Server.
Any help or suggestion would be greatly helpful, thanks in advance
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Just install the SQL Server 2008 (Express edition = it is free!) and you'll be able to restore this DB.
PS; check whether it was SQL Server 2008 or SQL Server 2008 R2.
|
|
|
|
|
Read the message again; it is very clear on what is wrong and how to solve it.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
It says but little confused about how is it going to resolve by just installing lower version - lol, thanks for helping for both of you.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You can't restore a backup from a higher version on a lower version.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi,
I have a Contolflow Task in my SSIS Package, which sends Emails, and because we don't need it and msdb.dbo.sp_send_dbmail is disabled on our SQL Server, I have first disabled the Controlflow task that sends Email using msdb.dbo.sp_send_dbmail (basically Execute SQL Task).
It was working fine on my local machine, when I deployed the Package on to a Server as SQL Agent Job, Job didn't execute threw error. Then I disabled the Email task on Package deployed it again, but surprisingly, Job is giving the same error as " msdb.dbo.sp_send_dbmail is disabled"
Then I completely deleted the Task and deployed again, still it is giving me the same error, to check when I opened the Package in notepad the task still showing.
Is there any way we can completely get rid of the task from SSIS Package, any help would be very helpful - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi,
I am executing the following query, its taking too long to execute, can anybody give me any advice what can I do to improve its performance, because its taking hours of time to execute. Thanks in advance.
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
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
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
Here are the following Views:
CREATE VIEW [dbo].[vw_SDMCPhaseTwoParentLevelXML]
AS
SELECT
DISTINCT 'DMH' AS [Owner]
,'' AS [Billing_NPI]
,SUBSTRING(RTRIM(LE.TaxId), 1, 9) AS [Billing_EIN]
,'' AS DirectIndicator
,CO.PK_Geographic_Location_Code AS [County_Code]
,CO.County_Name AS [TP_Name]
FROM
dbo.LegalEntity LE<br />
INNER JOIN dbo.county CO
ON LE.FKCountyLKPId = CO.PKCountyId
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND LEFT(LegalEntityNbr,3) IN ('000')
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
GO
CREATE VIEW [dbo].[vw_ServiceFacilityXML5010]
AS
SELECT
distinct VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]<br />
,US.NPINumber AS [Service_Facility_NPI]
, 'true' AS [County_Owned_Indicator]
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
INNER JOIN dbo.ProviderService CSD
ON CSD.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCS
ON MCS.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCSD
ON MCSD.FKProviderId = MCS.FKProviderId
INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
ON MS.Code= SFCMMS.MCModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.ProviderType FT
ON C.ProviderTypeId = FT.ProviderTypeId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) IN ('000', 'AFC')
AND LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')<br />
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
UNION ALL<br />
SELECT
distinct VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]<br />
,US.NPINumber AS [Service_Facility_NPI]
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
INNER JOIN dbo.ProviderService CSD
ON CSD.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCS
ON MCS.FKProviderId = CS.FKProviderId
LEFT OUTER JOIN dbo.ProviderService MCSD
ON MCSD.FKProviderId = MCS.FKProviderId
INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
ON MS.Code= SFCMMS.MCModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.ProviderType FT
ON C.ProviderTypeId = FT.ProviderTypeId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) NOT IN ('000', 'AFC','HFP', '00F')<br />
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
GO
CREATE VIEW [dbo].[vw_ServicesXML5010]
AS
SELECT
VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]
,US.NPINumber AS [Service_Facility_NPI]
,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
,SP.ProcedureCode AS [Procedure_Code]
,SP.Modifier1 as Modifier1
,ISNULL(SP.Modifier2, '') AS [Modifier2]
,ISNULL(SP.Modifier3, '') AS [Modifier3]
,ISNULL(SP.Modifier4, '') AS [Modifier4]
,CASE
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
ELSE ''
END Place_Of_Service
, '' AS Taxonomy_Code
,ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '') AS [From_Date]
,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date]
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId
INNER JOIN dbo.ServFuncCatMCModeOfService SFC
ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.Code = SFC.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
(
SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
)
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.FacilityDesignationLKP FT
ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.FKServFuncCatMCModeOfServiceId = SFC.PKServFuncCatMCModeOfServiceId
AND SP.Modifier1 <> 'HK'
UNION ALL
SELECT
VW.Billing_EIN AS [Billing_EIN]
,CO.PK_Geographic_Location_Code AS [County_Code]
,US.NPINumber AS [Service_Facility_NPI]
,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
,SP.ProcedureCode AS [Procedure_Code]
,SP.Modifier1 as Modifier1
,ISNULL(SP.Modifier2, '') AS [Modifier2]
,ISNULL(SP.Modifier3, '') AS [Modifier3]
,ISNULL(SP.Modifier4, '') AS [Modifier4]
,CASE
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
ELSE ''
END Place_Of_Service
, '' AS Taxonomy_Code
,CASE
WHEN (SP.ProcedureCode = 'H2015' or SP.ProcedureCode = 'T1017')AND SP.Modifier1 = 'HK' --all Katie A. Services start on 20130101
THEN '20130101'
ELSE
ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '')
END [From_Date]
,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] -- end date is not changing for Katie A. services. Just eliminate the rows that have an end date
FROM
dbo.Provider C
INNER JOIN dbo.ProviderDate CD
ON CD.FKProviderId = C.PKProviderId
INNER JOIN dbo.ProviderService CS
ON CS.FKProviderDateId = CD.PKProviderDateId
INNER JOIN dbo.ServFuncCatMCModeOfService SFC
ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
INNER JOIN dbo.ModeOfServiceLKP MS
ON MS.Code = SFC.ModeOfServiceCode
LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
ON MMS.Code = SFC.MCModeOfServiceCode
INNER JOIN dbo.NPIAssociation USP
ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
(
SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
)
INNER JOIN dbo.NPI US
ON US.PKNPIId = USP.FKNPIId
INNER JOIN dbo.LegalEntity LE
ON LE.PKLegalEntityId = C.FKLegalEntityId
--INNER JOIN dbo.usrTaxid TX
-- ON LE.FKTaxId = TX.PKTaxId
INNER JOIN dbo.County CO
ON C.FKCountyLKPId = CO.PKCountyId
INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
ON CO.PK_Geographic_Location_Code = VW.county_code
INNER JOIN dbo.FacilityDesignationLKP FT
ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
INNER JOIN dbo.MCMSSFCrosswalkLKP SP
ON MMS.Code = SP.MCModeOfServiceCode
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND MMS.Code IS NOT NULL
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
AND (SP.Modifier1 = 'HK' and (CS.MCEndDate is null or (CS.MCBeginDate >= '20130101' or CS.MCEndDate > '20130101')))
GO
Any help would be greatly helpful, thanks in advance.
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
This is getting old now. When posting SQL queries and asking "what is wrong" or "how does this not work" or ... well, anything, you should include some sample data for all of the tables used in your query.
Nobody wants to use up their spare time (which is how this forum works) traipsing through 200+ lines of code in the off-chance they might spot something that could be improved.
Help us to help you.
|
|
|
|
|
Some of the tables in that Query have 15 Columns too, I don't know how can I put all that Data here on the forum itself, it will be more huge message than currently, I am assuming this current message itself is too big.
What I am asking is, are there any places that I can be suspicious of taking more time than normal. Any help would be very very helpful buddy.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: What I am asking is, are there any places that I can be suspicious of taking more time than normal.
The usual things.
Converting varchars to dates is a big timewaster, having magical numbers shows a design-flaw, and every function that is called on each value will slow the thing down fast.
8 Ways to Fine-tune your SQL Queries (for production databases) | Sisense[^]
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi all. I was in a meeting recently where the chair was extolling the virtues of Graph databases. So much so that he asserted, if they’d been invented, Ralph Kimball would never have become involved with dimensional modelling and would have used graph databases instead. I have been involved with dimensional modelling (and cubes) for many years, but can’t claim to have built a Graph database. However, I understand their strength is in identifying relationships. If you’re working with data that is hierarchical in nature and, the relationships are evident, then Graph is not appropriate. Would be interested in you views. Cheers
|
|
|
|
|
DADom wrote: So much so that he asserted, if they’d been invented, Ralph Kimball would never have become involved with dimensional modelling and would have used graph databases instead.
Keep any evangelists away from these discussions. You don't need a salesman on your team.
DADom wrote: Would be interested in you views.
My strength is identifying relationships too, so I prefer to store relational data in a RDBMS in a structure that is at least in BCNF. It is a proven way which guarantees consistency and correctness.
According to the wiki, a graph db excells where data is "difficult to model" (and I agree with the comment "according to whom"). So, to evaluate it, you come looking for anecdotes.
I suggest you make a list (and check it twice) with advantages and disadvantages of both, and decide which are more important to you. THAT is the correct way out of these discussions - you will need to justify your choice, which is not done by pointing to an anecdote on codeproject, but by showing which of the two choices is the better fit in your specific case.
Personally, I'd say that the choice is obvious; Most RDBMSs are stable and well-optimized for data storage and retrieval, and there are a lot of resources on SQL92 (talk about backward compatibility eh?) Graph-databases would require something else than SQL probably, adding to the costs (and riscs).
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Eddy Vluggen wrote: According to the wiki,
Where is the wiki? Regards.
|
|
|
|
|
John 13564154 wrote:
Where is the wiki? Regards.
We don't have one; I was referring to this[^] wikipedia article.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I have a table product, columns:id,date,product,productname
I want to create a insert trigger, when I insert id,date,product, the productname will auto update in to the table(the product=productname... I don't know how to make the equation, please help me.
sorry for my terrible English.
modified 22-May-18 5:39am.
|
|
|
|
|
Why have two columns with the same data?
Have you tried anything at all yet?
|
|
|
|
|
CREATE TRIGGER (Transact-SQL) | Microsoft Docs[^]
CREATE TRIGGER YourTriggerName
ON YourTableName
AFTER insert, update
As
BEGIN
SET NOCOUNT ON;
If UPDATE(FK)
BEGIN
UPDATE
T
SET
RedundantDuplicateColumn = T2.Name
FROM
YourTable As T
INNER JOIN inserted As I
ON I.PK = T.PK
LEFT JOIN YourOtherTable As T2
ON T2.PK = T.FK
;
END;
END
Replace the dummy table and column names with the real table and column names.
Remember that triggers will fire once per batch, not once per row. There could be multiple rows in the inserted table.
But as CHill60 said, duplicating the product name in both tables seems like a bad design. If you have the ID of the product, you can simply join to the product table to get the name on demand.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This is a REALLY BAD design, instead of having a separate table for productname create a view
create view vwProductName
as
select distinct product as productname from product
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Am I missing something ... where's the table for the ProductName mentioned?
|
|
|
|
|
OP wrote:
the productname will auto update in to the table(the product=productname.
So yes I think you missed this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's an ERP bill, customer want to input the batch number, but they don't want to use any rules, just want to fetch the bill number into that field.
|
|
|
|
|
The comments still stand - there should be no reason to store the productname in a different table especially as it is user input and free form.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a query that is using FOR XML PATH, when the child elements has values then its giving the Child elements, but when there are no values coming out of query for the Child elements, its finishing the X-path by putting and end tag (\), but I want the all the elements to be created if there are values are not, it doesn't have to repeat the elements as if there are values but at least one occurrence has to be there even if there are values or don't have values.
Below are the example for it.
Case 1 is the file that it is creating with all the values and elements, there are elements that are repeated I am not putting all that xml here to reduce the size, but I want to create all the elements of the xml even if there are values or no values at least one occurrence.
<TB_BILLING_PROVIDER_FILES>
<TB_TRADING_PARTNER>
<Owner>DMH</Owner>
<TP_EIN>9xxxxxxx1</TP_EIN>
<TP_NAME>Alameda</TP_NAME>
<County_Code>01</County_Code>
<TB_BILLING_PROVIDER_FILE>
<TB_SERVICE_FACILITY>
<Service_Facility_NPI>xxxxxxxx7</Service_Facility_NPI>
<County_Owned_Indicator>true</County_Owned_Indicator>
<Service xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Procedure_Code>Hxxxxx</Procedure_Code>
<Modifier1>HE</Modifier1>
<Modifier2>TG</Modifier2>
<Modifier3/>
<Modifier4/>
<From_Date>19811201</From_Date>
<To_Date>20030623</To_Date>
<Revenue_Code/>
<Place_Of_Service/>
<Taxonomy_Code/>
</Service>
</TB_SERVICE_FACILITY>
</TB_BILLING_PROVIDER_FILE>
</TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>
The Case 2 xml is as below:
<TB_BILLING_PROVIDER_FILES>
<TB_TRADING_PARTNER>
<Owner>DMH</Owner>
<TP_EIN>xxxxxxxxx</TP_EIN>
<TP_NAME>Alameda</TP_NAME>
<County_Code>01</County_Code>
<TB_BILLING_PROVIDER_FILE/>
</TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>
The sql script for the case 1 is:
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')
The sql script for the case 2 is:
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
dbo.vw_SDMCPhaseTwoParentLevelXML 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')
I am not understanding how are these two different, I just want to create the complete xml at least with one occurrence of all the elements, can anybody please help me, any help a suggestion a link or code snippet, anything helps. Thanks in advance buddies.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
