I want to select all the node names beneath a given node

T

thecolour

Dear all

I have a diffgram:


<?xml version="1.0" standalone="yes"?>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<PolicyInwards diffgr:id="PolicyInwards1" msdata:rowOrder="0"
diffgr:hasChanges="modified">
<PolicyID>POL0001</PolicyID>
<InsuredName>Insured Co 111</InsuredName>
<InceptDate>2007-01-01T00:00:00+00:00</InceptDate>
<BrokerCode>BKR1</BrokerCode>
<UW>U1</UW>
<LimitCCY>GBP</LimitCCY>
<LimitAmt>1000001.00</LimitAmt>
<SharePctg>0.10</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards2" msdata:rowOrder="1"
diffgr:hasChanges="modified">
<PolicyID>POL00010</PolicyID>
<InsuredName>Insured Co 10</InsuredName>
<InceptDate>2007-01-15T00:00:00+00:00</InceptDate>
<BrokerCode>BKR10</BrokerCode>
<UW>U10</UW>
<LimitCCY>USD</LimitCCY>
<LimitAmt>1000010.00</LimitAmt>
<SharePctg>1.00</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards4" msdata:rowOrder="3"
diffgr:hasChanges="inserted">
<PolicyID>POL100040</PolicyID>
<InsuredName>Insured Co 100040</InsuredName>
<InceptDate>2281-05-19T14:44:29.63+01:00</InceptDate>
<BrokerCode>BKR 100040</BrokerCode>
<UW>UW 100040</UW>
<LimitCCY>GBP</LimitCCY>
<LimitAmt>1100040.00</LimitAmt>
<SharePctg>11.00</SharePctg>
<Status>L</Status>
</PolicyInwards>
</NewDataSet>
<diffgr:before>
<PolicyInwards diffgr:id="PolicyInwards1" msdata:rowOrder="0">
<PolicyID>POL0001</PolicyID>
<InsuredName>Insured Co 1</InsuredName>
<InceptDate>2007-01-01T00:00:00+00:00</InceptDate>
<BrokerCode>BKR1</BrokerCode>
<UW>U1</UW>
<LimitCCY>GBP</LimitCCY>
<LimitAmt>1000001.00</LimitAmt>
<SharePctg>0.10</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards2" msdata:rowOrder="1">
<PolicyID>POL00010</PolicyID>
<InsuredName>Insured Co 10</InsuredName>
<InceptDate>2007-01-02T00:00:00+00:00</InceptDate>
<BrokerCode>BKR10</BrokerCode>
<UW>U10</UW>
<LimitCCY>USD</LimitCCY>
<LimitAmt>1000010.00</LimitAmt>
<SharePctg>1.00</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards3" msdata:rowOrder="2">
<PolicyID>POL00019</PolicyID>
<InsuredName>Insured Co 19</InsuredName>
<InceptDate>2007-01-19T00:00:00+00:00</InceptDate>
<BrokerCode>BKR19</BrokerCode>
<UW>U19</UW>
<LimitCCY>EUR</LimitCCY>
<LimitAmt>1000019.00</LimitAmt>
<SharePctg>1.90</SharePctg>
<Status>L</Status>
</PolicyInwards>
</diffgr:before>
</diffgr:diffgram>


and I am trying to select all the child node names out of each
<PolicyInwards> node. Ideally I want to programatically compare the
values entered in the child nodes across different <PolicyInwards>
nodes, without having to hard code the node names themselves.


Eg I want to compare the values in elements <InsuredName>,
<InceptDate>, etc under /diffgr:diffgram/NewDataSet/
PolicyInwards[diffgr:id='PolicyInwards2'] with the same element values
under /diffgr:diffgram/Diffgr:before/
PolicyInwards[diffgr:id='PolicyInwards2'], extract the element name,
and the values of each under NewDataSet and Diffgr:before for each
element


The best I have come up with so far is


declare namespace diffgr = "urn:schemas-microsoft-com:xml-diffgram-
v1";

<ReconResults>

{
:)this sections gets all the differences between rows present in
both datasets :)
for $PolicyInwardsDifference in /diffgr:diffgram/NewDataSet/
PolicyInwards
where $PolicyInwardsDifference/diffgr:id[text() = /diffgr:diffgram/
diffgr:before/PolicyInwards/diffgr:id/text()]
return
if( $PolicyInwardsDifference/@diffgr:hasChanges[text() =
'modified'] )
then
<Difference>
<PKValue>
{$PolicyInwardsDifference/PolicyID/text()}
</PKValue>
{
for $Column in $PolicyInwardsDifference/node()

return
<Column>
<ColumnName>{$PolicyInwardsDifference[node() =
$Column]}</ColumnName>
<ValueBefore>(need to get this)</ValueSource>
<ValueAfter>(need to get this)</ValueTarget>
</Column>
}
</Difference>
else ()
}


</ReconResults>


I am happy to clarify if someone out there would be so kind as to
help
me out.


Many thanks
Tom
 
T

thecolour

Dear all

I have a diffgram:

<?xml version="1.0" standalone="yes"?>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<PolicyInwards diffgr:id="PolicyInwards1" msdata:rowOrder="0"
diffgr:hasChanges="modified">
<PolicyID>POL0001</PolicyID>
<InsuredName>Insured Co 111</InsuredName>
<InceptDate>2007-01-01T00:00:00+00:00</InceptDate>
<BrokerCode>BKR1</BrokerCode>
<UW>U1</UW>
<LimitCCY>GBP</LimitCCY>
<LimitAmt>1000001.00</LimitAmt>
<SharePctg>0.10</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards2" msdata:rowOrder="1"
diffgr:hasChanges="modified">
<PolicyID>POL00010</PolicyID>
<InsuredName>Insured Co 10</InsuredName>
<InceptDate>2007-01-15T00:00:00+00:00</InceptDate>
<BrokerCode>BKR10</BrokerCode>
<UW>U10</UW>
<LimitCCY>USD</LimitCCY>
<LimitAmt>1000010.00</LimitAmt>
<SharePctg>1.00</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards4" msdata:rowOrder="3"
diffgr:hasChanges="inserted">
<PolicyID>POL100040</PolicyID>
<InsuredName>Insured Co 100040</InsuredName>
<InceptDate>2281-05-19T14:44:29.63+01:00</InceptDate>
<BrokerCode>BKR 100040</BrokerCode>
<UW>UW 100040</UW>
<LimitCCY>GBP</LimitCCY>
<LimitAmt>1100040.00</LimitAmt>
<SharePctg>11.00</SharePctg>
<Status>L</Status>
</PolicyInwards>
</NewDataSet>
<diffgr:before>
<PolicyInwards diffgr:id="PolicyInwards1" msdata:rowOrder="0">
<PolicyID>POL0001</PolicyID>
<InsuredName>Insured Co 1</InsuredName>
<InceptDate>2007-01-01T00:00:00+00:00</InceptDate>
<BrokerCode>BKR1</BrokerCode>
<UW>U1</UW>
<LimitCCY>GBP</LimitCCY>
<LimitAmt>1000001.00</LimitAmt>
<SharePctg>0.10</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards2" msdata:rowOrder="1">
<PolicyID>POL00010</PolicyID>
<InsuredName>Insured Co 10</InsuredName>
<InceptDate>2007-01-02T00:00:00+00:00</InceptDate>
<BrokerCode>BKR10</BrokerCode>
<UW>U10</UW>
<LimitCCY>USD</LimitCCY>
<LimitAmt>1000010.00</LimitAmt>
<SharePctg>1.00</SharePctg>
<Status>L</Status>
</PolicyInwards>
<PolicyInwards diffgr:id="PolicyInwards3" msdata:rowOrder="2">
<PolicyID>POL00019</PolicyID>
<InsuredName>Insured Co 19</InsuredName>
<InceptDate>2007-01-19T00:00:00+00:00</InceptDate>
<BrokerCode>BKR19</BrokerCode>
<UW>U19</UW>
<LimitCCY>EUR</LimitCCY>
<LimitAmt>1000019.00</LimitAmt>
<SharePctg>1.90</SharePctg>
<Status>L</Status>
</PolicyInwards>
</diffgr:before>
</diffgr:diffgram>

and I am trying to select all the child node names out of each
<PolicyInwards> node. Ideally I want to programatically compare the
values entered in the child nodes across different <PolicyInwards>
nodes, without having to hard code the node names themselves.

Eg I want to compare the values in elements <InsuredName>,
<InceptDate>, etc under /diffgr:diffgram/NewDataSet/
PolicyInwards[diffgr:id='PolicyInwards2'] with the same element values
under /diffgr:diffgram/Diffgr:before/
PolicyInwards[diffgr:id='PolicyInwards2'], extract the element name,
and the values of each under NewDataSet and Diffgr:before for each
element

The best I have come up with so far is

declare namespace diffgr = "urn:schemas-microsoft-com:xml-diffgram-
v1";

<ReconResults>

{
:)this sections gets all the differences between rows present in
both datasets :)
for $PolicyInwardsDifference in /diffgr:diffgram/NewDataSet/
PolicyInwards
where $PolicyInwardsDifference/diffgr:id[text() = /diffgr:diffgram/
diffgr:before/PolicyInwards/diffgr:id/text()]
return
if( $PolicyInwardsDifference/@diffgr:hasChanges[text() =
'modified'] )
then
<Difference>
<PKValue>
{$PolicyInwardsDifference/PolicyID/text()}
</PKValue>
{
for $Column in $PolicyInwardsDifference/node()

return
<Column>
<ColumnName>{$PolicyInwardsDifference[node() =
$Column]}</ColumnName>
<ValueBefore>(need to get this)</ValueSource>
<ValueAfter>(need to get this)</ValueTarget>
</Column>
}
</Difference>
else ()
}

</ReconResults>

I am happy to clarify if someone out there would be so kind as to
help
me out.

Many thanks
Tom
Click to expand...

OK I have made some more progress. My XQuery now looks like this:

<ReconResults>

{
for $PolicyInwardsDifference in /diffgr:diffgram/NewDataSet/
PolicyInwards
return
if( $PolicyInwardsDifference/@diffgr:hasChanges[text() =
'modified'] )
then
<Difference>
<PKValue>
{$PolicyInwardsDifference/PolicyID/text()}
</PKValue>
{
for $Column in $PolicyInwardsDifference/*/name()
where $PolicyInwardsDifference/@diffgr:id = /diffgr:diffgram/
diffgr:before/PolicyInwards/@diffgr:id
return
<Column>
<ColumnName>{$Column}</ColumnName>
<ValueSource>{$PolicyInwardsDifference/*/node()}</ValueSource>
<ValueTarget>{/diffgr:diffgram/diffgr:before/
PolicyInwards[@diffgr:id = $PolicyInwardsDifference/@diffgr:id]/*/
node()}</ValueTarget>
</Column>
}
</Difference>
else ()
}
</ReconResults>

and the output I get is this:

<ReconResults>
<Difference>
<PKValue>POL0001</PKValue>
<Column>
<ColumnName>PolicyID</ColumnName>
<ValueSource>POL0001 Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001 Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>InsuredName</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>InceptDate</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>BrokerCode</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>UW</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>LimitCCY</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>LimitAmt</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>SharePctg</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
<Column>
<ColumnName>Status</ColumnName>
<ValueSource>POL0001Insured Co
1112007-01-01T00:00:00+00:00BKR1U1 GBP1000001.000.10L</ValueSource>
<ValueTarget>POL0001Insured Co 12007-01-01T00:00:00+00:00BKR1U1
GBP1000001.000.10L</ValueTarget>
</Column>
</Difference>
<Difference>
<PKValue>POL00010</PKValue>
<Column>
<ColumnName>PolicyID</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>InsuredName</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>InceptDate</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>BrokerCode</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>UW</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>LimitCCY</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>LimitAmt</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>SharePctg</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
<Column>
<ColumnName>Status</ColumnName>
<ValueSource>POL00010Insured Co
102007-01-15T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueSource>
<ValueTarget>POL00010Insured Co
102007-01-02T00:00:00+00:00BKR10U10USD1000010.001.00L</ValueTarget>
</Column>
</Difference>
<Missing>
<MissingIn>Source</MissingIn>
<PKValue>POL100040</PKValue>
</Missing>
<Missing>
<MissingIn>Target</MissingIn>
<PKValue>POL00019</PKValue>
</Missing>
</ReconResults>

which is ALMOST what I want but I can't prevent the values in the
<ValueSource/> and <ValueTarget/> nodes to stop repeating.

I would massivly appraciate any help at all.

Best
Tom
 
T

thecolour

I actually sorted this out with the following xquery:

<ReconResults>


{
:)this sections gets all the differences between rows present in
both datasets :)
for $PolicyInwardsDifference in /diffgr:diffgram/NewDataSet/
PolicyInwards
:)
where
$PolicyInwardsDifference/diffgr:id = /diffgr:diffgram/
diffgr:before/PolicyInwards/diffgr:id
:)
return
if( $PolicyInwardsDifference/@diffgr:hasChanges[text() =
'modified'] )
then
<Difference>
<PKValue>
{$PolicyInwardsDifference/PolicyID/text()}
</PKValue>
{
for $Column in $PolicyInwardsDifference/*/name()
where $PolicyInwardsDifference/@diffgr:id = /
diffgr:diffgram/diffgr:before/PolicyInwards/@diffgr:id

return
if ($PolicyInwardsDifference/*[name() = $Column]/node() !
= /diffgr:diffgram/diffgr:before/PolicyInwards[@diffgr:id =
$PolicyInwardsDifference/@diffgr:id]/*[name() = $Column]/node())
then
<Column>
<ColumnName>{$Column}</ColumnName>
<ValueSource>{$PolicyInwardsDifference/*[name() =
$Column]/node()}</ValueSource>
<ValueTarget>{/diffgr:diffgram/diffgr:before/
PolicyInwards[@diffgr:id = $PolicyInwardsDifference/@diffgr:id]/
*[name() = $Column]/node()}</ValueTarget>
</Column>
else ()
}
</Difference>
else ()
}

</ReconResults>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I want to select all the node names beneath a given node 0
SOAP posts to .net web services 0
comp.lang.c Answers to Frequently Asked Questions (FAQ List) 15

Members online

No members online now.
Total: 30 (members: 0, guests: 30)
Robots: 153

Forum statistics

Threads
473,756
Messages
2,569,534
Members
45,007
Latest member
OrderFitnessKetoCapsules

Latest Threads

Top