Click here to Skip to main content
13,664,842 members
   

Database

 
QuestionRe: How to create a insert trigger on just one table? Pin
CHill6022-May-18 1:34
protectorCHill6022-May-18 1:34 
AnswerRe: How to create a insert trigger on just one table? Pin
Richard Deeming22-May-18 2:30
mvpRichard Deeming22-May-18 2:30 
AnswerRe: How to create a insert trigger on just one table? Pin
Mycroft Holmes22-May-18 14:09
professionalMycroft Holmes22-May-18 14:09 
GeneralRe: How to create a insert trigger on just one table? Pin
CHill6022-May-18 22:56
protectorCHill6022-May-18 22:56 
GeneralRe: How to create a insert trigger on just one table? Pin
Mycroft Holmes23-May-18 13:20
professionalMycroft Holmes23-May-18 13:20 
GeneralRe: How to create a insert trigger on just one table? Pin
Member23622-May-18 23:04
memberMember23622-May-18 23:04 
GeneralRe: How to create a insert trigger on just one table? Pin
Mycroft Holmes23-May-18 13:23
professionalMycroft Holmes23-May-18 13:23 
QuestionFOR XML PATH is not creating Child elements if they are coming with null values Pin
indian14316-May-18 12:39
memberindian14316-May-18 12:39 
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."
AnswerRe: FOR XML PATH is not creating Child elements if they are coming with null values Pin
CHill6022-May-18 1:32
protectorCHill6022-May-18 1:32 
QuestionEquivalent of the following query without FOR XML PATH Pin
indian14314-May-18 12:03
memberindian14314-May-18 12:03 
SuggestionRe: Equivalent of the following query without FOR XML PATH Pin
CHill6015-May-18 0:11
protectorCHill6015-May-18 0:11 
GeneralRe: Equivalent of the following query without FOR XML PATH Pin
indian14315-May-18 6:39
memberindian14315-May-18 6:39 
GeneralRe: Equivalent of the following query without FOR XML PATH Pin
CHill6015-May-18 22:11
protectorCHill6015-May-18 22:11 
GeneralRe: Equivalent of the following query without FOR XML PATH Pin
indian14317-May-18 14:56
memberindian14317-May-18 14:56 
RantLatest Oracle Connection method Pin
Mycroft Holmes6-May-18 14:56
professionalMycroft Holmes6-May-18 14:56 
GeneralRe: Latest Oracle Connection method Pin
Jörgen Andersson16-May-18 0:20
protectorJörgen Andersson16-May-18 0:20 
GeneralRe: Latest Oracle Connection method Pin
Mycroft Holmes16-May-18 0:27
professionalMycroft Holmes16-May-18 0:27 
GeneralRe: Latest Oracle Connection method Pin
Jörgen Andersson16-May-18 0:37
protectorJörgen Andersson16-May-18 0:37 
QuestionSQL 2016 Optimization ... Pin
Member 138060854-May-18 1:10
memberMember 138060854-May-18 1:10 
RantRe: SQL 2016 Optimization ... Pin
CHill604-May-18 1:13
protectorCHill604-May-18 1:13 
AnswerRe: SQL 2016 Optimization ... Pin
Eddy Vluggen5-May-18 1:14
professionalEddy Vluggen5-May-18 1:14 
QuestionSQL 2016 Optimization ... Pin
Member 138060854-May-18 1:10
memberMember 138060854-May-18 1:10 
QuestionHow to stop execution of other tasks in SSIS if a script task gets Dts.TaskResult = (int)ScriptResults.Failure; Pin
indian1432-May-18 14:12
memberindian1432-May-18 14:12 
AnswerSelf answered just if somebody has similar problem it would be helpful. How to stop execution of other tasks in SSIS if a script task gets Dts.TaskResult = (int)ScriptResults.Failure; Pin
indian1433-May-18 8:41
memberindian1433-May-18 8:41 
QuestionInsert dynamic SQL Values into Temp Table Pin
indian14326-Apr-18 7:03
memberindian14326-Apr-18 7:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 15 Aug 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid