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

Discussion in 'XML' started by thecolour, Jun 26, 2007.

  1. thecolour

    thecolour Guest

    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
     
    thecolour, Jun 26, 2007
    #1
    1. Advertising

  2. thecolour

    thecolour Guest

    On 26 Jun, 17:08, thecolour <> wrote:
    > 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


    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
     
    thecolour, Jun 27, 2007
    #2
    1. Advertising

  3. thecolour

    thecolour Guest

    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>
     
    thecolour, Jun 27, 2007
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?ISO-8859-1?Q?=22Gabri=EBl=40Home=22?=

    Strange white block beneath the page in FF

    =?ISO-8859-1?Q?=22Gabri=EBl=40Home=22?=, Jan 30, 2006, in forum: HTML
    Replies:
    5
    Views:
    412
    Mark Parnell
    Feb 1, 2006
  2. Lewis G. Pringle, Jr.
    Replies:
    0
    Views:
    612
    Lewis G. Pringle, Jr.
    Sep 30, 2003
  3. chiara
    Replies:
    6
    Views:
    490
    Barry Schwarz
    Oct 6, 2005
  4. thecolour
    Replies:
    0
    Views:
    384
    thecolour
    Jun 26, 2007
  5. Matt
    Replies:
    2
    Views:
    215
    Roland Hall
    Jan 11, 2004
Loading...

Share This Page