当我从SQL Server查询数据AS XML时,它通常会生成重复的XML节点。通常我可以调整查询来消除这种情况,但并非总是如此。如果不能,我就使用这样的XML:
<Xml>
<House houseId="3" address="123 Main">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
</Dog>
<Dog dogId="13" name="Rover">
<Flea fleaId="23" name="Poindexter" />
</Dog>
</House>
<House houseId="3" address="123 Main">
<Human humanId="9" name="Mr. Johnson">
<Child childId="11" name="Susie" />
</Human>
<Human humanId="9" name="Mr. Johnson">
<Child childId="31" name="Sandy" />
</Human>
</House>
<House houseId="5" address="987 Wall">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
</Dog>
<Dog dogId="13" name="Rover">
<Flea fleaId="19" name="Wilhelm" />
</Dog>
</House>
</Xml>
注意这里有两个
<Xml>
<House houseId="3" address="123 Main">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
<Flea fleaId="23" name="Poindexter" />
</Dog>
<Human humanId="9" name="Mr. Johnson">
<Child childId="11" name="Susie" />
<Child childId="31" name="Sandy" />
</Human>
</House>
<House houseId="5" address="987 Wall">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
<Flea fleaId="19" name="Wilhelm" />
</Dog>
</House>
</Xml>
不仅两个相同的House节点被合并,重复的Dog和Human节点也被合并。但请注意
因为XML是由SQL生成的,所以XSLT将处理许多不同名称和排列的节点。因此,我不能硬编码节点名称。但我可以确信,每个节点都有一个相应的id属性,该属性将包含一个数值。例如:
我还知道根节点没有属性,所以我可以开始处理作为根节点的子节点。
我的策略是为每个Node创建一个xsl:key,其中节点的键值是其祖先节点与id值的连接。示例键值在
下面的注释中<Xml>
<House houseId="3" address="123 Main"><!--"houseId=3"-->
<Dog dogId="13" name="Rover" ><!--"houseId=3;dogId=13"-->
<Flea fleaId="17" name="Chester" /><!--"houseId=3;dogId=13;fleaId=17"-->
</Dog>
<Dog dogId="13" name="Rover" ><!--"houseId=3;dogId=13"-->
<Flea fleaId="23" name="Poindexter" /><!--"houseId=3;dogId=13;fleaId=23"-->
</Dog>
</House>
<House houseId="3" address="123 Main" ><!--"houseId=3"-->
<Human humanId="9" name="Mr. Johnson" ><!--"houseId=3;humanId=9"-->
<Child childId="11" name="Susie" /><!--"houseId=3;humanId=9;childId=11"-->
</Human>
<Human humanId="9" name="Mr. Johnson"><!--"houseId=3;humanId=9"-->
<Child childId="31" name="Sandy" /><!--"houseId=3;humanId=9;childId=31"-->
</Human>
</House>
<House houseId="5" address="987 Wall" ><!--"houseId=5"-->
<Dog dogId="13" name="Rover"><!--"houseId=5;dogId=13"-->
<Flea fleaId="17" name="Chester" /><!--"houseId=5;dogId=13;fleaId=17"-->
</Dog>
<Dog dogId="13" name="Rover"><!--"houseId=5;dogId=13"-->
<Flea fleaId="19" name="Wilhelm" /><!--"houseId=5;dogId=13;fleaId=19"-->
</Dog>
</House>
</Xml>
所以,
& lt; Xml> & lt;房子houseId ="3"祝辞& lt;狗dogId ="13"的名字="漫游者"比;
houseId = 3; dogId = 13
与
& lt; Xml> & lt;房子houseId ="5"祝辞& lt;狗dogId ="13"的名字="漫游者"在
houseId = 5; dogId = 13
这样,就可以组合重复的(兄弟)节点。不幸的是,我正在努力理解如何使用XSL和xslt:键来实现这一点。
这一转换:
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:my="my:my">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>
<xsl:template match="/*" priority="3">
<xsl:sequence select="my:grouping(., *)"/>
</xsl:template>
<xsl:function name="my:grouping" as="element()*">
<xsl:param name="pElem" as="element()"/>
<xsl:param name="pChildren" as="element()*"/>
<xsl:element name="{name($pElem)}" namespace="{namespace-uri($pElem)}">
<xsl:apply-templates select="$pElem/@*"/>
<xsl:for-each-group select="$pChildren" group-by="my:signature(.)">
<xsl:copy>
<xsl:apply-templates select="@*|node()[not(self::*)]"/>
<xsl:apply-templates select=
"my:grouping(., current-group()/*)/*"/>
</xsl:copy>
</xsl:for-each-group>
</xsl:element>
</xsl:function>
<xsl:function name="my:signature" as="xs:string">
<xsl:param name="pElem" as="element()"/>
<xsl:variable name="vAttibs" as="xs:string*">
<xsl:perform-sort select="$pElem/@*">
<xsl:sort select="name()"/>
</xsl:perform-sort>
</xsl:variable>
<xsl:sequence select=
"string-join((name($pElem)
,for $at in $vAttibs
return concat($at, '+', $pElem/@*[name()=$at])
)
,'|')"/>
</xsl:function>
</xsl:stylesheet>
<Xml>
<House houseId="3" address="123 Main">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
</Dog>
<Dog dogId="13" name="Rover">
<Flea fleaId="23" name="Poindexter" />
</Dog>
</House>
<House houseId="3" address="123 Main">
<Human humanId="9" name="Mr. Johnson">
<Child childId="11" name="Susie" />
</Human>
<Human humanId="9" name="Mr. Johnson">
<Child childId="31" name="Sandy" />
</Human>
</House>
<House houseId="5" address="987 Wall">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
</Dog>
<Dog dogId="13" name="Rover">
<Flea fleaId="19" name="Wilhelm" />
</Dog>
</House>
</Xml>
生成所需的正确结果:
<Xml>
<House houseId="3" address="123 Main">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester"/>
<Flea fleaId="23" name="Poindexter"/>
</Dog>
<Human humanId="9" name="Mr. Johnson">
<Child childId="11" name="Susie"/>
<Child childId="31" name="Sandy"/>
</Human>
</House>
<House houseId="5" address="987 Wall">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester"/>
<Flea fleaId="19" name="Wilhelm"/>
</Dog>
</House>
</Xml>
和这个扩展的XML文档(添加了文本节点):
<Xml>
<House houseId="3" address="123 Main">
<Dog dogId="13" name="Rover">
Dog named Rover
<Flea fleaId="17" name="Chester">Regular dog flee</Flea>
</Dog>
<Dog dogId="13" name="Rover">
<Flea fleaId="23" name="Poindexter">Flea named Poindexter</Flea>
</Dog>
</House>
<House houseId="3" address="123 Main">
<Human humanId="9" name="Mr. Johnson">
<Child childId="11" name="Susie">Susan Johnson</Child>
</Human>
<Human humanId="9" name="Mr. Johnson">
<Child childId="31" name="Sandy">Sandy Johnson</Child>
</Human>
</House>
<House houseId="5" address="987 Wall">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester" />
</Dog>
<Dog dogId="13" name="Rover">
<Flea fleaId="19" name="Wilhelm" />
</Dog>
</House>
</Xml>
再次生成正确的结果:
<Xml>
<House houseId="3" address="123 Main">
<Dog dogId="13" name="Rover">
Dog named Rover
<Flea fleaId="17" name="Chester">Regular dog flee</Flea>
<Flea fleaId="23" name="Poindexter">Flea named Poindexter</Flea>
</Dog>
<Human humanId="9" name="Mr. Johnson">
<Child childId="11" name="Susie">Susan Johnson</Child>
<Child childId="31" name="Sandy">Sandy Johnson</Child>
</Human>
</House>
<House houseId="5" address="987 Wall">
<Dog dogId="13" name="Rover">
<Flea fleaId="17" name="Chester"/>
<Flea fleaId="19" name="Wilhelm"/>
</Dog>
</House>
</Xml>
:
我们使用两个函数:my:signature()
和my:grouping()
:
my:signature()
为每个元素创建签名——这是元素名称和所有attrName+value对的管道分隔字符串,按attrName排序。my:grouping()
使用my:signature()
进行正确分组。它有第二个参数,包含要分组的元素。