Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tidy up repeated data in XML 1

Status
Not open for further replies.
skippabloy

skippabloy

Technical User
Feb 9, 2007
5
GB
Hi.
I have this........

</persons>

<persons><person><id>510023609</id>

<first_name>A</first_name>

<last_name>Schmolck</last_name>

<description>ENGINEERING &amp; C</description>

<access_categories><access_category><name>Student - General</name>

</access_category>

</access_categories>

</person>

</persons>

<persons><cards><card><number>40045464</number>

<format_name>exeter_uni</format_name>

<person_id>510023609</person_id>

</card>

</cards>

</persons>

<persons><person><id>510023609</id>

<first_name>A</first_name>

<last_name>Schmolck</last_name>

<description>ENGINEERING &amp; C</description>

<access_categories><access_category><name>HARRISON STAFF</name>

</access_category>

</access_categories>

</person>

</persons>

<persons><cards><card><number>40045464</number>

<format_name>exeter_uni</format_name>

<person_id>510023609</person_id>

</card>

</cards>

</persons>

<persons><person><id>510023609</id>

<first_name>A</first_name>

<last_name>Schmolck</last_name>

<description>ENGINEERING &amp; C</description>

<access_categories><access_category><name>Special Needs Zone</name>

</access_category>

</access_categories>

</person>

</persons>

<persons><cards><card><number>40045464</number>

<format_name>exeter_uni</format_name>

<person_id>510023609</person_id>

</card>

</cards>

</persons>





And I need this!!





<persons><person><id>510023609</id>

<first_name>A</first_name>

<last_name>Schmolck</last_name>

<description>ENGINEERING &amp; C</description>

<access_categories>

<access_category><name>HARRISON STAFF</name>

</access_category>

<access_category><name>Student - General</name>

</access_category>

<access_category><name>Special Needs Zone</name>

</access_category>

</access_categories>

</person>

</persons>

<persons><cards><card><number>40045464</number>

<format_name>exeter_uni</format_name>

<person_id>510023609</person_id>

</card>

</cards>

</persons>

does anyone know of a way to merge the repeated data???
 
Sort by date Sort by votes
Yes. XSLT.

Take this tutorial and this one too.

You have a requirement for grouping. Unless you have XSLT 2.0 available (few do), you will need to use the Muench method described by me in this thread: thread426-1210886. Look in the second half of the thread or just search for the word "Muench".

This should get you started... :-D

Tom Morrison
 
Upvote 0 Downvote
Thanks for your help....

But.... Im new to xml and even after reading through the tutorials im still none the wiser...........

anything else you can give me to put me in the right direction..

Thanks again...

 
Upvote 0 Downvote
Download a free trial version of Stylus Studio or Altova XML Suite. (I prefer Stylus Studio, but it really is personal preference. Stylus Studio have several levels, at different price points. Pick the one that suits your need.)

Do you understand from your reading of tutorials that XSLT is a language for describing transformations of XML documents into other XML documents (or HTML, or text)?

As a starting point, consider the following XSLT. Here is your original input:
Code: 
<?xml version="1.0"?>
<root>
	<persons>
		<person>
			<id>510023609</id>
			<first_name>A</first_name>
			<last_name>Schmolck</last_name>
			<description>ENGINEERING &amp; C</description>
			<access_categories>
				<access_category>
					<name>Student - General</name>
				</access_category>
			</access_categories>
		</person>
	</persons>
	<persons>
		<cards>
			<card>
				<number>40045464</number>
				<format_name>exeter_uni</format_name>
				<person_id>510023609</person_id>
			</card>
		</cards>
	</persons>
	<persons>
		<person>
			<id>510023609</id>
			<first_name>A</first_name>
			<last_name>Schmolck</last_name>
			<description>ENGINEERING &amp; C</description>
			<access_categories>
				<access_category>
					<name>HARRISON STAFF</name>
				</access_category>
			</access_categories>
		</person>
	</persons>
	<persons>
		<cards>
			<card>
				<number>40045464</number>
				<format_name>exeter_uni</format_name>
				<person_id>510023609</person_id>
			</card>
		</cards>
	</persons>
	<persons>
		<person>
			<id>510023609</id>
			<first_name>A</first_name>
			<last_name>Schmolck</last_name>
			<description>ENGINEERING &amp; C</description>
			<access_categories>
				<access_category>
					<name>Special Needs Zone</name>
				</access_category>
			</access_categories>
		</person>
	</persons>
	<persons>
		<cards>
			<card>
				<number>40045464</number>
				<format_name>exeter_uni</format_name>
				<person_id>510023609</person_id>
			</card>
		</cards>
	</persons>
</root>

Here is an XSLT that reorders the persons and cards:
Code: 
<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]

<xsl:template match="/">
<new-doc>
	<xsl:for-each select="root/persons/person">
		<xsl:copy-of select="."/>
	</xsl:for-each>
	<xsl:for-each select="root/persons/cards">
		<xsl:copy-of select="."/>
	</xsl:for-each>
</new-doc>
</xsl:template>

</xsl:stylesheet>

Here is the output:
Code: 
<?xml version='1.0' ?>
<new-doc><person>
			<id>510023609</id>
			<first_name>A</first_name>
			<last_name>Schmolck</last_name>
			<description>ENGINEERING &amp; C</description>
			<access_categories>
				<access_category>
					<name>Student - General</name>
				</access_category>
			</access_categories>
		</person><person>
			<id>510023609</id>
			<first_name>A</first_name>
			<last_name>Schmolck</last_name>
			<description>ENGINEERING &amp; C</description>
			<access_categories>
				<access_category>
					<name>HARRISON STAFF</name>
				</access_category>
			</access_categories>
		</person><person>
			<id>510023609</id>
			<first_name>A</first_name>
			<last_name>Schmolck</last_name>
			<description>ENGINEERING &amp; C</description>
			<access_categories>
				<access_category>
					<name>Special Needs Zone</name>
				</access_category>
			</access_categories>
		</person><cards>
			<card>
				<number>40045464</number>
				<format_name>exeter_uni</format_name>
				<person_id>510023609</person_id>
			</card>
		</cards><cards>
			<card>
				<number>40045464</number>
				<format_name>exeter_uni</format_name>
				<person_id>510023609</person_id>
			</card>
		</cards><cards>
			<card>
				<number>40045464</number>
				<format_name>exeter_uni</format_name>
				<person_id>510023609</person_id>
			</card>
		</cards></new-doc>

If you understand this, then we can move on to grouping information from multiple nodes of the input document into a single node in the output document.

Tom Morrison
 
Upvote 0 Downvote
I looked at the Top XML tutorial, thanks, that was very informative. And since you didn't get anything for all your work in this thread, have a star!!

<.

 
Upvote 0 Downvote
Thanks again for your help...

Right so far so good.... I understand that!!!!!!

whats next???

Nick
 
Upvote 0 Downvote
Now to use the Muench Method for grouping. See the previous thread reference for an explanation.
Code: 
<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:output indent="yes"/>

<xsl:key name="personKey" match="root/persons/person" use="id"/>
<xsl:key name="cardKey"   match="root/persons/cards"  use="number"/>

<xsl:template match="/">
<new-doc>
	<persons>
    <!-- The outer for-each selects the first person node for every unique id value.  
	     In other words, the outer for-each will iterate exactly once for each unique value of id. -->
    <xsl:for-each select="//persons/person[generate-id(.)=generate-id(key('personKey', id)[1])]">
        <xsl:sort select="id"/>
		<person>
			<id><xsl:value-of select="id"/></id>
			<first_name><xsl:value-of select="first_name"/></first_name>
			<last_name><xsl:value-of select="last_name"/></last_name>
			<description><xsl:value-of select="description"/></description>
			<access_categories>
        <!-- The inner for-each iterates over all nodes that match the outer for-each id value -->
        	<xsl:for-each select="key('personKey', id)">
				<access_category>
					<name><xsl:value-of select="access_categories/access_category/name"/></name>
				</access_category>
			</xsl:for-each>
			</access_categories>
		</person>
    </xsl:for-each>
	[COLOR=red]<!-- grouping cards is left as an exercise -->[/color]
	</persons>
</new-doc>
</xsl:template>

</xsl:stylesheet>

Produces this.
Code: 
<?xml version='1.0' ?>
<new-doc>
  <persons>
    <person>
      <id>510023609</id>
      <first_name>A</first_name>
      <last_name>Schmolck</last_name>
      <description>ENGINEERING &amp; C</description>
      <access_categories>
        <access_category>
          <name>Student - General</name>
        </access_category>
        <access_category>
          <name>HARRISON STAFF</name>
        </access_category>
        <access_category>
          <name>Special Needs Zone</name>
        </access_category>
      </access_categories>
    </person>
  </persons>
</new-doc>

Tom Morrison
 
Upvote 0 Downvote
Wicked!!! I understand..

It nearly works now but it creates different nodes for the different access_catergories but instead of adding the acccess category name it puts the persons surname??? any ideas whats wrong???

slightly modded style.xsl

Code: 
<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:output indent="yes"/>

<xsl:key name="personKey" match="arxdata/persons/person" use="id"/>
<xsl:key name="cardKey"   match="arxdata/persons/cards"  use="number"/>

<xsl:template match="/">
<arxdata>
    <!-- The outer for-each selects the first person node for every unique id value.  
         In other words, the outer for-each will iterate exactly once for each unique value of id. -->
    <xsl:for-each select="/arxdata/persons/person[generate-id(.)=generate-id(key('personKey', id)[1])]">
        <persons>
	<xsl:sort select="id"/>
        <person>
            <id><xsl:value-of select="id"/></id>
            <first_name><xsl:value-of select="first_name"/></first_name>
            <last_name><xsl:value-of select="last_name"/></last_name>
            <description><xsl:value-of select="description"/></description>
            <access_categories>
        <!-- The inner for-each iterates over all nodes that match the outer for-each id value -->
            <xsl:for-each select="key('personKey', id)">
                <access_category>
                    <name><xsl:value-of select="access_categories/access_category/name"/></name>
                </access_category>
            </xsl:for-each>
            </access_categories>
        </person>
	</persons>
    </xsl:for-each>
    <!-- grouping cards is left as an exercise -->
</arxdata>
</xsl:template>

</xsl:stylesheet>

output.xml

Code: 
<?xml version='1.0' ?>
<arxdata>
  <persons>
    <person>
      <id>510023609</id>
      <first_name>A</first_name>
      <last_name>Schmolck</last_name>
      <description>ENGINEERING &amp; C</description>
      <access_categories>
        <access_category>
          <name>Schmolck</name>
        </access_category>
        <access_category>
          <name>Schmolck</name>
        </access_category>
        <access_category>
          <name>Schmolck</name>
        </access_category>
      </access_categories>
    </person>
  </persons>



Im nearly there!!! Thanks again for your help.


Nick
 
Upvote 0 Downvote
Nick,

Are you still having this problem? It seems impossible from the XSL you have shown, given the previous input XML document, since the context of the inner for-each is <person> node. You can confirm this by placing some dubug info...
Code: 
            <xsl:for-each select="key('personKey', id)">
                [COLOR=red]<IAmIn><xsl:value-of select="local-name(.)"/></IAmIn>[/color]
                <access_category>
This will show the name of the context node...

Tom Morrison
 
Upvote 0 Downvote
Sorry the problem was caused by my imput xml..

Many thanks for your help and I now understand!!!!!

Thanks again
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

smak1
  • Locked
  • Question
add structure to XML
Replies
0
Views
690
smak1
smak1
Bijan Borazjani
  • Locked
  • Question
XML to PDF
Replies
0
Views
786
Bijan Borazjani
Bijan Borazjani
cdr_greg
  • Locked
  • Question
Display XML Line Breaks in Microsoft Word Content Controls
Replies
0
Views
1K
cdr_greg
cdr_greg
198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
742
atlopes
atlopes
SitesMasstec
  • Locked
  • Question
XML data not displaying
Replies
2
Views
760
jjgupta915
jjgupta915

Part and Inventory Search

Sponsor

Back
Top