XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Christian MenapaceSubject: 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>
-----------------------------------------------------

Postnext
Minollo I.Subject: rdbxml : big query, bad result
Author: Minollo I.
Date: 08 Feb 2006 11:38 AM
Christian,
your "bad result" output does look very strange given the query you are running; this might be a problem with the underlying SQL/XML engine Stylus Studio is running. Is there any chance you can email us a (partial, or zipped) copy of the Access database you are accessing? That would allow us to troubleshoot this issue much more efficiently.

Thanks,
Minollo

Postnext
Christian MenapaceSubject: rdbxml : big query, bad result
Author: Christian Menapace
Date: 13 Feb 2006 04:04 AM
Thanks for your fast response.
As the data is confidential I have make an extract .

If you apply the two queries the result is different.
One have two nodes assessment
The second have one node assessment.

Regards

Christian MENAPACE


UnknownOpRisk.zip

Postnext
Minollo I.Subject: rdbxml : big query, bad result
Author: Minollo I.
Date: 13 Feb 2006 09:22 AM
Thanks Christian; we are investigating the problem.

Minollo

Posttop
Minollo I.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

 
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.