XQuery - accessing node names and values

Post by **thecolour** » Wed Jun 27, 2007 12:53 pm

Hi everyone, wondering if I could get some pointers!

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>

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>
</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 appreciate any help at all.

Best
Tom

Post by **thecolour** » Wed Jun 27, 2007 1:47 pm

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>