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
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
- Stylus Studio Technical Forum (14621)
-> - Stylus Studio - Registrar en o... (1)
-> + Stylus Studio - Registrar en o... (2)
-> + Can a pipeline send a file by ... (2)
-> + After Updateing WIN10 to WIN11... (12)
-> + Where do I add the custom java... (3)
-> + Where is the Diagram tab? (5)
-> + Applying XSLT to Word DOCX/XML (2)
-> - CSV conversion via ConvertToXM... (1)
-> + Text symbols in SS not same as... (4)
-> + Exposing xquery as webservice ... (6)
-> + Syntax Identifier (2)
-> + Saving a Converted XML as an X... (5)
-> + Output document cannot be pars... (4)
-> - Archiving output from conversi... (1)
-> + EDIFACT guideline from Stylus ... (3)
-> + CSV file putting all the data ... (5)
-> + Can't install Home version 64b... (5)
-> + presale - Can I covers this sc... (5)
-> + Problem with UNB (5)
-> + Splitting EDIFACT files pipeli... (4)
-- [1-20] [21-40] [41-60] Next
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
+ XQuery Help and Discussion (2016)
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
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

   
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.