?
Solved

xquery - how to use a recurring value only once

Posted on 2008-10-26
3
Medium Priority
?
375 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 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses
Course of the Month9 days, 10 hours left to enroll

609 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