none
Sql Transport Schema Generation Wizard warns of syntax error with XML PATH RRS feed

  • Question

  • Hey guys. I'm trying to use the Wizard to generate a schema using the sql code below. This code works in query analyzer, but I get a syntax error when trying to use it in the wizard. Any ideas as to why this is happening? Thanks, Paul

    select  Claim.*,
         ClaimDetail.Claimno as 'ClaimDetail/Claimno',
         ClaimDetail.LineNumber as 'ClaimDetail/LineNumber',
         ClaimDetail.ServiceDateFrom as 'ClaimDetail/ServiceDateFrom',
         ClaimDetail.ServiceDateTo as 'ClaimDetail/ServiceDateTo',
         ClaimDetail.AssessmentDate as 'ClaimDetail/AssessmentDate',
         ClaimDetail.FacilityCode as 'ClaimDetail/FacilityCode',
         ClaimDetail.RevenueCode as 'ClaimDetail/RevenueCode',
         ClaimDetail.ProcedureCode as 'ClaimDetail/ProcedureCode',
         ClaimDetail.Amount as 'ClaimDetail/Amount',
         ClaimDetail.Unit as 'ClaimDetail/Unit',
         ClaimDetail.Quantity as 'ClaimDetail/Quantity',
         ClaimDetail.UnitRate as 'ClaimDetail/UnitRate',
         ClaimDetail.NonCovered as 'ClaimDetail/NonCovered',
         ClaimDetail.Paid as 'ClaimDetail/Paid',
         ClaimDetail.PlaceOfService as 'ClaimDetail/PlaceOfService',
         ClaimDetail.Modifier1 as 'ClaimDetail/Modifier1',
         ClaimDetail.Modifier2 as 'ClaimDetail/Modifier2',
         ClaimDetail.Modifier3 as 'ClaimDetail/Modifier3',
         ClaimDetail.Modifier4 as 'ClaimDetail/Modifier4',
         ClaimDetail.DiagPointer1 as 'ClaimDetail/DiagPointer1',
         ClaimDetail.DiagPointer2 as 'ClaimDetail/DiagPointer2',
         ClaimDetail.DiagPointer3 as 'ClaimDetail/DiagPointer3',
         ClaimDetail.DiagPointer4 as 'ClaimDetail/DiagPointer4',
         ClaimDetail.ToothNumber as 'ClaimDetail/ToothNumber',
         ClaimDetail.Surface as 'ClaimDetail/Surface',
         ClaimDetail.EmergencyIndicator as 'ClaimDetail/EmergencyIndicator',
         ClaimDetail.RepricingMethodology as 'ClaimDetail/RepricingMethodology',
         ClaimDetail.RepricedAmount as 'ClaimDetail/RepricedAmount',
         ClaimDetail.SavingsAmount as 'ClaimDetail/SavingsAmount',
         ClaimDetail.RepricerID as 'ClaimDetail/RepricerID',
         ClaimDetail.RejectReason as 'ClaimDetail/RejectReason',
         ClaimDetail.ApprovedAmount as 'ClaimDetail/ApprovedAmount',
         ClaimDetail.OtherPayerPaid as 'ClaimDetail/OtherPayerPaid',
         ClaimDetail.OtherPayerAdjustmentReasonGroup as 'ClaimDetail/OtherPayerAdjustmentReasonGroup',
         ClaimDetail.OtherPayerAdjustmentReason as 'ClaimDetail/OtherPayerAdjustmentReason',
         ClaimDetail.OtherPayerAdjustmentAmount as 'ClaimDetail/OtherPayerAdjustmentAmount',
         ClaimDetail.Remark as 'ClaimDetail/Remark',
         ClaimOcc.Claimno as 'ClaimOcc/Claimno',
         ClaimOcc.Typ as 'ClaimOcc/Typ',
         ClaimOcc.Seq as 'ClaimOcc/Seq',
         ClaimOcc.OccCode as 'ClaimOcc/OccCode',
         ClaimOcc.OccFromDate as 'ClaimOcc/OccFromDate',
         ClaimOcc.OccThruDate as 'ClaimOcc/OccThruDate'
    from Z837i_Claim Claim
    inner join Z837i_ClaimDetail ClaimDetail on Claim.Claimno = ClaimDetail.Claimno
    left outer join Z837i_ClaimOcc ClaimOcc on Claim.Claimno = ClaimOcc.Claimno
    for XML PATH('Claim'),  ELEMENTS XSINIL;

    When I use subqueries, the generated schema will show elements of claim (c) but not of cd or occ.

    select c.*, (select cd.* from Z837I_claimdetail cd where cd.Claimno
    = c.ClaimNo for XML auto, type, elements),
    (select occ.* from Z837I_ClaimOcc occ where occ.ClaimNo = c.ClaimNo for XML auto, type, elements)
    from z837i_claim c
    for xml auto, type, elements

    Friday, April 29, 2011 8:26 PM

All replies

  • I know the wizard will not work on certain characters, I wonder if the "/" is one of these on the column names of the first query. Usually the generation wizard will show a red X you can double click on to get the error or show the error message of what went wrong.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, May 2, 2011 3:44 PM
    Moderator
  • I just learned that the sql adapter will not work with xml path.

    When I use xml auto, it doesn't like the aliases for the two subqueries. It seems it only wants to work with aliases for tables.

    The problem is that xml auto makes the third subquery a child of the second query. I want it to be a child of the driving table and a sibling of the second subquery.

    Monday, May 2, 2011 8:12 PM