 next
|
  Subject: rdbxml : big query, bad result
Author: Christian Menapace
Date: 08 Feb 2006 09:03 AM
|
Hi,
I try to extract a xml message of Access DB.
with the sub query directly applied, I get only one assessment tag (like i want), but with the big query I have 2 tags assessment with a big mess of tags in it. like this
Here is the queries :
-------------------- Bad query --------------------
SELECT XMLELEMENT( NAME "potential_incident",
XMLATTRIBUTES(PCQ_PIN.ID_PI AS "external_id"),
XMLELEMENT( NAME "affected_process",
XMLATTRIBUTES(PCQ_PIN.ProcessID AS "id"),
XMLELEMENT( NAME "key_process",
XMLATTRIBUTES (PCQ_PIN.KeyProcessID AS "id"),
XMLELEMENT( NAME "name", PCQ_PIN.KeyProcessName),
XMLELEMENT( NAME "primary_process",
XMLATTRIBUTES (PCQ_PIN.KeyProcessID AS "id"),
XMLELEMENT( NAME "name", PCQ_PIN.KeyProcessName),
XMLELEMENT( NAME "pma",
XMLATTRIBUTES (PCQ_PIN.PMAID AS id),
XMLELEMENT( NAME "name", PCQ_PIN.PMA)
)
)
)
),
XMLELEMENT( NAME "event",
XMLELEMENT( NAME "name", PCQ_PIN.EventName),
XMLELEMENT( NAME "description", PCQ_PIN.EventDescription),
XMLELEMENT( NAME "event_type",
XMLATTRIBUTES(PCQ_PIN.EventTypeNo AS "id")
),
XMLELEMENT( NAME "location_of_event",
(SELECT
XMLELEMENT( NAME "bu",
XMLELEMENT( NAME "org_entity",
XMLATTRIBUTES(PCQ_BU_PI.EntityID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_PI.EntityName)
),
XMLELEMENT( NAME "geo_entity",
XMLATTRIBUTES(PCQ_BU_PI.LocationID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_PI.LocationName)
)
)
FROM PCQ_BU_POTENTIAL_INCIDENT PCQ_BU_PI
WHERE PCQ_BU_PI.ID_PI = PCQ_PIN.ID_PI
)
)
),
(SELECT
XMLELEMENT( NAME "cause",
XMLELEMENT( NAME "name", PCQ_CAU.CauseName),
XMLELEMENT( NAME "description", PCQ_CAU.CauseDescription),
XMLELEMENT( NAME "causal_category",
XMLATTRIBUTES (PCQ_CAU.CausalCategoryID AS "id")
),
XMLELEMENT( NAME "location_of_cause",
(SELECT
XMLELEMENT( NAME "bu",
XMLELEMENT( NAME "org_entity",
XMLATTRIBUTES(PCQ_BU_CA.EntityID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_CA.EntityName)
),
XMLELEMENT( NAME "geo_entity",
XMLATTRIBUTES(PCQ_BU_CA.LocationID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_CA.LocationName)
)
)
FROM PCQ_BU_CAUSE PCQ_BU_CA
WHERE PCQ_BU_CA.ID_Cause = PCQ_CAU.ID_Cause
)
)
)
FROM PCQ_CAUSE PCQ_CAU
WHERE PCQ_CAU.ID_PI = PCQ_PIN.ID_PI
),
(SELECT
XMLELEMENT( NAME "control",
XMLELEMENT( NAME "name", PCQ_CON.ControlName),
XMLELEMENT( NAME "type",
XMLATTRIBUTES (PCQ_CON.ControlTypeID AS "id")
),
XMLELEMENT( NAME "performer",
(SELECT
XMLELEMENT( NAME "bu",
XMLELEMENT( NAME "org_entity",
XMLATTRIBUTES(PCQ_BU_CO.EntityID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_CO.EntityName)
),
XMLELEMENT( NAME "geo_entity",
XMLATTRIBUTES(PCQ_BU_CO.LocationID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_CO.LocationName)
)
)
FROM PCQ_BU_CONTROL PCQ_BU_CO
WHERE PCQ_BU_CO.ID_Control = PCQ_CON.ID_Control
)
)
)
FROM PCQ_CONTROL PCQ_CON
WHERE PCQ_CON.ID_PI = PCQ_PIN.ID_PI
),
(SELECT
XMLELEMENT( NAME "quantification_factor",
XMLATTRIBUTES(PCQ_PIN4.ID_ADomain AS "id"),
XMLELEMENT( NAME "name", PCQ_PIN4.QuantificationFactorName),
XMLELEMENT( NAME "type",
XMLATTRIBUTES( PCQ_PIN4.QuantificationFactorType AS "id")
),
XMLELEMENT( NAME "comment", PCQ_PIN4.QuantificationFactorComment )
)
FROM PCQ_POTENTIAL_INCIDENT PCQ_PIN4
WHERE PCQ_PIN4.ID_PI = PCQ_PIN.ID_PI
),
(SELECT
XMLELEMENT( NAME "effect",
XMLELEMENT( NAME "name", PCQ_EFF.EffectName),
XMLELEMENT( NAME "calculation_description", PCQ_EFF.DescriptionFinancialImpactCalculation),
XMLELEMENT( NAME "effect_type",
XMLATTRIBUTES(PCQ_EFF.EffectTypeID AS "id")
)
)
FROM PCQ_EFFECT PCQ_EFF
WHERE PCQ_EFF.ID_PI = PCQ_PIN.ID_PI
),
(SELECT
XMLELEMENT( NAME "assessment",
XMLATTRIBUTES (PCQ_ASS.ID_Assess AS "id"),
XMLELEMENT( NAME "name", PCQ_ASS.name),
XMLELEMENT( NAME "description", PCQ_ASS.DescriptionOfAssessment),
XMLELEMENT( NAME "applicability_domain",
(SELECT
XMLELEMENT( NAME "bu",
XMLELEMENT( NAME "org_entity",
XMLATTRIBUTES(PCQ_BU_ASS.EntityID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_ASS.EntityName)
),
XMLELEMENT( NAME "geo_entity",
XMLATTRIBUTES(PCQ_BU_ASS.LocationID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_ASS.LocationName)
)
)
FROM PCQ_BU_ASSESSMENT PCQ_BU_ASS
WHERE PCQ_BU_ASS.ID_PI = PCQ_ASS.ID_PI
)
),
(SELECT DISTINCT
XMLELEMENT( NAME "retained_control",
XMLATTRIBUTES(PCQ_ASS4.ControlID AS "id"),
XMLELEMENT( NAME "name", PCQ_ASS4.ControlName),
XMLELEMENT( NAME "evaluation",
XMLATTRIBUTES (PCQ_ASS4.EvaluationId AS "id")
)
)
FROM PCQ_ASSESSMENT_2 PCQ_ASS4
WHERE PCQ_ASS4.ID_PI = PCQ_ASS.ID_PI
AND PCQ_ASS4.ID_Assess = PCQ_ASS.ID_Assess
AND PCQ_ASS4.ControlID IS NOT NULL
),
(SELECT
XMLELEMENT( NAME "likely_case",
XMLATTRIBUTES('1' AS "type"),
XMLELEMENT( NAME "total_severity", PCQ_QC10.TotalSeverity),
XMLELEMENT( NAME "frequency", PCQ_QC10.Frequency_),
XMLELEMENT( NAME "description", PCQ_QC10.Assumption),
(SELECT
XMLELEMENT( NAME "retained_quantification_factor",
XMLELEMENT( NAME "value",
XMLATTRIBUTES( PCQ_QC11.Measure AS "unit"),
PCQ_QC11.Value
),
XMLELEMENT( NAME "comment", PCQ_QC11.Comments)
)
FROM PCQ_VALUE_QUANTIFICATIONCASE PCQ_QC11
WHERE PCQ_QC11.ID_Case = PCQ_QC10.ID_Case
),
(SELECT DISTINCT
XMLELEMENT( NAME "retained_effect",
XMLELEMENT( NAME "severity", PCQ_QC12.Severity),
XMLELEMENT( NAME "comment", PCQ_QC12.CommentCase)
)
FROM PCQ_QUANTIFICATION_CASE_2 PCQ_QC12
WHERE PCQ_QC12.ID_PI = PCQ_QC10.ID_PI
)
)
FROM PCQ_QUANTIFICATION_CASE_1 PCQ_QC10
WHERE PCQ_QC10.ID_PI = PCQ_ASS.ID_PI
AND PCQ_QC10.QuantificationCaseTypeName = 'LC'
),
(SELECT
XMLELEMENT( NAME "worst_case",
XMLATTRIBUTES('2' AS "type"),
XMLELEMENT( NAME "total_severity", PCQ_QC20.TotalSeverity),
XMLELEMENT( NAME "frequency", PCQ_QC20.Frequency_),
XMLELEMENT( NAME "description", PCQ_QC20.Assumption),
(SELECT XMLELEMENT( NAME "retained_quantification_factor",
XMLELEMENT( NAME "value",
XMLATTRIBUTES( PCQ_QC21.Measure AS "unit"),
PCQ_QC21.Value
),
XMLELEMENT( NAME "comment", PCQ_QC21.Comments)
)
FROM PCQ_VALUE_QUANTIFICATIONCASE PCQ_QC21
WHERE PCQ_QC21.ID_Case = PCQ_QC20.ID_Case
),
(SELECT DISTINCT
XMLELEMENT( NAME "retained_effect",
XMLELEMENT( NAME "severity", PCQ_QC22.Severity),
XMLELEMENT( NAME "comment", PCQ_QC22.CommentCase)
)
FROM PCQ_QUANTIFICATION_CASE_2 PCQ_QC22
WHERE PCQ_QC22.ID_PI = PCQ_QC20.ID_PI
)
)
FROM PCQ_QUANTIFICATION_CASE_1 PCQ_QC20
WHERE PCQ_QC20.ID_PI = PCQ_ASS.ID_PI
AND PCQ_QC20.QuantificationCaseTypeName = 'WC'
)
)
FROM PCQ_ASSESSMENT_1 PCQ_ASS
WHERE PCQ_ASS.ID_PI = PCQ_PIN.ID_PI
)
)
FROM PCQ_POTENTIAL_INCIDENT PCQ_PIN
WHERE PCQ_PIN.ID_PI = 181
---------------------------------------------------
-------------------- Good sub query --------------------
SELECT
XMLELEMENT( NAME "assessment",
XMLATTRIBUTES (PCQ_ASS.ID_Assess AS "id"),
XMLELEMENT( NAME "name", PCQ_ASS.name),
XMLELEMENT( NAME "description", PCQ_ASS.DescriptionOfAssessment),
XMLELEMENT( NAME "applicability_domain",
(SELECT
XMLELEMENT( NAME "bu",
XMLELEMENT( NAME "org_entity",
XMLATTRIBUTES(PCQ_BU_ASS.EntityID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_ASS.EntityName)
),
XMLELEMENT( NAME "geo_entity",
XMLATTRIBUTES(PCQ_BU_ASS.LocationID AS "id"),
XMLELEMENT( NAME "name", PCQ_BU_ASS.LocationName)
)
)
FROM PCQ_BU_ASSESSMENT PCQ_BU_ASS
WHERE PCQ_BU_ASS.ID_PI = PCQ_ASS.ID_PI
)
),
(SELECT DISTINCT
XMLELEMENT( NAME "retained_control",
XMLATTRIBUTES(PCQ_ASS4.ControlID AS "id"),
XMLELEMENT( NAME "name", PCQ_ASS4.ControlName),
XMLELEMENT( NAME "evaluation",
XMLATTRIBUTES (PCQ_ASS4.EvaluationId AS "id")
)
)
FROM PCQ_ASSESSMENT_2 PCQ_ASS4
WHERE PCQ_ASS4.ID_PI = PCQ_ASS.ID_PI
AND PCQ_ASS4.ID_Assess = PCQ_ASS.ID_Assess
AND PCQ_ASS4.ControlID IS NOT NULL
),
(SELECT
XMLELEMENT( NAME "likely_case",
XMLATTRIBUTES('1' AS "type"),
XMLELEMENT( NAME "total_severity", PCQ_QC10.TotalSeverity),
XMLELEMENT( NAME "frequency", PCQ_QC10.Frequency_),
XMLELEMENT( NAME "description", PCQ_QC10.Assumption),
(SELECT
XMLELEMENT( NAME "retained_quantification_factor",
XMLELEMENT( NAME "value",
XMLATTRIBUTES( PCQ_QC11.Measure AS "unit"),
PCQ_QC11.Value
),
XMLELEMENT( NAME "comment", PCQ_QC11.Comments)
)
FROM PCQ_VALUE_QUANTIFICATIONCASE PCQ_QC11
WHERE PCQ_QC11.ID_Case = PCQ_QC10.ID_Case
),
(SELECT DISTINCT
XMLELEMENT( NAME "retained_effect",
XMLELEMENT( NAME "severity", PCQ_QC12.Severity),
XMLELEMENT( NAME "comment", PCQ_QC12.CommentCase)
)
FROM PCQ_QUANTIFICATION_CASE_2 PCQ_QC12
WHERE PCQ_QC12.ID_PI = PCQ_QC10.ID_PI
)
)
FROM PCQ_QUANTIFICATION_CASE_1 PCQ_QC10
WHERE PCQ_QC10.ID_PI = PCQ_ASS.ID_PI
AND PCQ_QC10.QuantificationCaseTypeName = 'LC'
),
(SELECT
XMLELEMENT( NAME "worst_case",
XMLATTRIBUTES('2' AS "type"),
XMLELEMENT( NAME "total_severity", PCQ_QC20.TotalSeverity),
XMLELEMENT( NAME "frequency", PCQ_QC20.Frequency_),
XMLELEMENT( NAME "description", PCQ_QC20.Assumption),
(SELECT XMLELEMENT( NAME "retained_quantification_factor",
XMLELEMENT( NAME "value",
XMLATTRIBUTES( PCQ_QC21.Measure AS "unit"),
PCQ_QC21.Value
),
XMLELEMENT( NAME "comment", PCQ_QC21.Comments)
)
FROM PCQ_VALUE_QUANTIFICATIONCASE PCQ_QC21
WHERE PCQ_QC21.ID_Case = PCQ_QC20.ID_Case
),
(SELECT DISTINCT
XMLELEMENT( NAME "retained_effect",
XMLELEMENT( NAME "severity", PCQ_QC22.Severity),
XMLELEMENT( NAME "comment", PCQ_QC22.CommentCase)
)
FROM PCQ_QUANTIFICATION_CASE_2 PCQ_QC22
WHERE PCQ_QC22.ID_PI = PCQ_QC20.ID_PI
)
)
FROM PCQ_QUANTIFICATION_CASE_1 PCQ_QC20
WHERE PCQ_QC20.ID_PI = PCQ_ASS.ID_PI
AND PCQ_QC20.QuantificationCaseTypeName = 'WC'
)
)
FROM PCQ_ASSESSMENT_1 PCQ_ASS
WHERE PCQ_ASS.ID_PI = 181
-----------------------------------------------------
---------------- Good result --------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<root>
<assessment id="7.0">
<name>FI Trading - Credit Repo LDN</name>
<description></description>
<applicability_domain>
<bu>
<org_entity id="1118920920538063">
<name>Credit Repo</name>
</org_entity>
<geo_entity id="1030073">
<name>London</name>
</geo_entity>
</bu>
</applicability_domain>
<retained_control id="3404">
<name>Repo Explain</name>
<evaluation id="12"></evaluation>
</retained_control>
<retained_control id="5057">
<name>Market Risk</name>
<evaluation id="13"></evaluation>
</retained_control>
<likely_case type="1">
<total_severity>0</total_severity>
<frequency>6</frequency>
<description>N/A (BFI import) - Average case: No assumptions available</description>
<retained_effect>
<severity></severity>
<comment>Average case: No assumptions available</comment>
</retained_effect>
<retained_effect>
<severity></severity>
<comment>Worst case: No assumptions available</comment>
</retained_effect>
</likely_case>
<worst_case type="2">
<total_severity>0</total_severity>
<frequency>1</frequency>
<description>N/A (BFI import) - Worst case: No assumptions available</description>
<retained_effect>
<severity></severity>
<comment>Average case: No assumptions available</comment>
</retained_effect>
<retained_effect>
<severity></severity>
<comment>Worst case: No assumptions available</comment>
</retained_effect>
</worst_case>
</assessment>
</root>
-----------------------------------------------------
--------------- Bad result ---------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<root>
<potential_incident external_id="181">
<affected_process id="1110074996556790">
<key_process id="1">
<name>n/a</name>
<primary_process id="1">
<name>n/a</name>
<pma id="1">
<name>n/a</name>
</pma>
</primary_process>
</key_process>
</affected_process>
<event>
<name>Mispricing</name>
<description>Mispricing on a credit repo, which is most frequently due to a confusion among the large number of quoted bonds (ex.: use of wrong curve on wrong bond)</description>
<event_type id="4030810"></event_type>
<location_of_event>
<bu>
<org_entity id="1118920920538063">
<name>Credit Repo</name>
</org_entity>
<geo_entity id="1030073">
<name>London</name>
</geo_entity>
</bu>
</location_of_event>
</event>
<control>
<name>Repo Explain</name>
<type id="28002"></type>
<performer>
<bu>
<org_entity id="1118920920538063">
<name>Credit Repo</name>
</org_entity>
<geo_entity id="1030073">
<name>London</name>
</geo_entity>
</bu>
<bu>
<org_entity id="1118920920538063">
<name>Credit Repo</name>
</org_entity>
<geo_entity id="1030081">
<name>New York</name>
</geo_entity>
</bu>
</performer>
</control>
<quantification_factor id="26">
<name>N/A (BFI import)</name>
<type id="1"></type>
<comment>N/A (BFI import)</comment>
</quantification_factor>
<assessment>
<likely_case>
<retained_effect>
<severity></severity>
<comment>Worst case: No assumptions available</comment>
</retained_effect>
<retained_effect>
<severity></severity>
<comment>Average case: No assumptions available</comment>
</retained_effect>
</likely_case>
<likely_case type="1">
<total_severity>0</total_severity>
<frequency>6</frequency>
<description>N/A (BFI import) - Average case: No assumptions available</description>
</likely_case>
<worst_case>
<retained_effect>
<severity></severity>
<comment>Worst case: No assumptions available</comment>
</retained_effect>
<retained_effect>
<severity></severity>
<comment>Average case: No assumptions available</comment>
</retained_effect>
<bu>
<org_entity id="1118920920538063">
<name>Credit Repo</name>
</org_entity>
<geo_entity id="1030073">
<name>London</name>
</geo_entity>
</bu>
</worst_case>
<retained_control id="3404">
<name>Repo Explain</name>
<evaluation id="12"></evaluation>
</retained_control>
<retained_control id="5057">
<name>Market Risk</name>
<evaluation id="13"></evaluation>
</retained_control>
<worst_case type="2">
<total_severity>0</total_severity>
<frequency>1</frequency>
<description>N/A (BFI import) - Worst case: No assumptions available</description>
</worst_case>
</assessment>
<assessment id="7">
<name>FI Trading - Credit Repo LDN</name>
<description></description>
<applicability_domain></applicability_domain>
</assessment>
</potential_incident>
</root>
-----------------------------------------------------
|
 top
|
  Subject: rdbxml : big query, bad result
Author: Minollo I.
Date: 13 Feb 2006 10:43 AM
|
Christian,
from a preliminary inspection of your testcase, it does look like the underlying Connect for SQL/XML module is getting confused. We will work with our DataDirect friends to better understand the issue.
In the meanwhile, we have noticed that trying to access the PCQ_ASS.ID_ASSESS ID from inside the subquery seems to fix the problem; so changing your query so that the "assessment" block starts with...
SELECT
XMLELEMENT( NAME "assessment",
XMLELEMENT( NAME "ID", PCQ_ASS.ID_ASSESS),
XMLELEMENT( NAME "name", PCQ_ASS.name),
...makes the query work for us.
Hope this helps,
Minollo
|
|
OpRisk.zip