Solved

xquery - how to use a recurring value only once

Posted on 2008-10-26
3
365 Views
Last Modified: 2013-11-11
I have a XML like this

<root>
<records>
<action id="1" site="A" item="abs"/>
<action id="2" site="A" item="hgt"/>
<action id="3" site="A" item="98uuj"/>
</records>
</root>

I want to make it into this one, where the site attribute will only appear once.
must use XQuery ...

<actions>
<site>A</site>
<items>
<item>
<id>1</id>
<code>abs</code>
</item>
<item>
<id>2</id>
<code>hgt</code>
</item>
<item>
<id>3</id>
<code>98uuj</code>
</item>
</items>
</actions>

0
Comment
Question by:shalomc
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
ID: 22807208
mmh, it sounds like this is more a task for XSLT instead of XQuery, but here you go
let $doc := doc("file:///d:/untitled1.xml")

return

<actions>

    {

        for $a in distinct-values($doc//action/@site)

        return 

            <site>{$a}</site>

    }

    {

        for $a in distinct-values($doc//action/@site)

        return 

            <items>

                {

                for $b in $doc//action

                where $b/@site = $a

                return

                    <item>

                        <id>{string($b/@id)}</id>

                        <code>{string($b/@item)}</code>

                    </item>

                }

               </items>

    }

</actions>

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 22807221
in case you are interested, this is the XSLT you would need
<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

    <xsl:output indent="yes"/>

    <xsl:template match="records">

        <actions>

            <xsl:for-each-group select="action" group-by="@site">

                <site><xsl:value-of select="@site"/></site>

                <items>

                    <xsl:for-each select="current-group()">

                        <item>

                            <id><xsl:value-of select="@id"/></id>

                            <code><xsl:value-of select="@item"/></code>

                        </item>

                    </xsl:for-each>

                </items>

            </xsl:for-each-group>

        </actions>

    </xsl:template>

</xsl:stylesheet>

Open in new window

0
 
LVL 33

Author Comment

by:shalomc
ID: 22807500
the distinct-value had me going :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now