修改XML以按名称对中级复杂类型元素进行排序 [英] Modify XML to Order Mid-level Complextype Elements by Name

问题描述

请参见此链接后续问题

See This link for follow-up question

我在SQL Server 2008 R2中有一个大型的FOR XML EXPLICIT查询，该查询创建了一个xml文件，但是由于某些中级元素的显示顺序不正确，因此无法针对我的XSD架构进行验证.这是一个XML文件示例，显示了我的意思:

I have a large FOR XML EXPLICIT query in SQL Server 2008 R2 that creates an xml file, but it is failing to validate against my XSD schema because some of the mid-level elements appear in the wrong order. Here is an example XML file to show what I mean:

<Things>
  <Thing>
    <Racecars>
      <Racecar>
        <Colour>Red</Colour>
        <Rockets>Y</Rockets>
      </Racecar>
      <Racecar>
        <Colour>Blue</Colour>
        <Rockets>N</Rockets>
      </Racecar>
    </Racecars>
  </Thing>
  <Thing>
    <Numbers>
      <Number>
        <NumericName>1</NumericName>
        <WrittenName>One</WrittenName>
      </Number>
      <Number>
        <NumericName>2</NumericName>
        <WrittenName>Two</WrittenName>
      </Number>
    </Numbers>
  </Thing>
  <Thing>
    <Letters>
      <Letter>
        <AlphaName>A</AlphaName>
        <PhoneticName>Ay</PhoneticName>
      </Letter>
      <Letter>
        <AlphaName>B</AlphaName>
        <PhoneticName>Bee</PhoneticName>
      </Letter>
    </Letters>
  </Thing>
</Things>

XML EXPLICIT命令的工作方式意味着包含某些属性的每个组都是按顺序排列的(在这种情况下是最底层)，但是我不知道如何控制属性(在这种情况下)正下方的顺序)事物"级别.没有SQL到ORDER BY的属性数据时，如何指定事物"的子元素按名称的字母顺序显示?

The way that the XML EXPLICIT command works means that each group containing some attributes are in order (the bottom level in this case), but I do not know how to control the order of the attributes directly below the (in this case) "Thing" Level. How can I specify that the child elements of "Thing" should appear in alphabetical order by name when there is no attribute data for SQL to ORDER BY?

正如我提到的，这是XML的一大部分，具有许多不同的元素，但并不总是全部出现，因此，如果有某种方法可以在一定级别上对所有子元素进行排序，而又不影响孙子元素而不必对每个元素名称进行硬编码，那真是太好了.

As I mentioned, this is a big bit of XML, with many different elements, which do not always all appear, so if there were some way to order all of the child elements at a certain level, without affecting the ordering of the grandchild elements without having to hard-code each element name, that would be really great.

我到处搜索都无济于事.我既查看了XML输出的事后xquery修改(在我看来这是最实用的解决方案，因为要排序的某些元素的minOccurs = 0)，或者作为FOR XML EXPLICIT命令设计的一部分(这似乎是最有效的方法，它可以防止而不是消除错误)，但是据我所知，似乎没有任何作用.

I have searched all over the place to no avail for a method. I have looked at both an ex post facto xquery modify on my XML output (which seems to me the most practical solution as some of the elements to be sorted have minOccurs = 0), or as part of the design of the FOR XML EXPLICIT command (which seems the most efficient as that way prevents rather than undoes the error) but, of what I have understood, nothing seems to work.

我当然可以从架构文件中删除约束，但是如果可能的话，我宁愿避免这种情况，因为屈服于不灵活性似乎是错误的选择方向.

I could, of course, remove the constraints from the schema file, but would much rather avoid this if at all possible as giving in to inexactitude seems the wrong direction to step.

使用上述文件，这是我认为理想的结果:

With the above file, this is the outcome I would consider ideal:

<Things>
  <Thing>
    <Letters>
      <Letter>
        <AlphaName>A</AlphaName>
        <PhoneticName>Ay</PhoneticName>
      </Letter>
      <Letter>
        <AlphaName>B</AlphaName>
        <PhoneticName>Bee</PhoneticName>
      </Letter>
    </Letters>
  </Thing>
  <Thing>
    <Numbers>
      <Number>
        <NumericName>1</NumericName>
        <WrittenName>One</WrittenName>
      </Number>
      <Number>
        <NumericName>2</NumericName>
        <WrittenName>Two</WrittenName>
      </Number>
    </Numbers>
  </Thing>
  <Thing>
    <Racecars>
      <Racecar>
        <Colour>Red</Colour>
        <Rockets>Y</Rockets>
      </Racecar>
      <Racecar>
        <Colour>Blue</Colour>
        <Rockets>N</Rockets>
      </Racecar>
    </Racecars>
  </Thing>
</Things>

如您所见，事物"下面的三个元素现在按字母顺序排序(字母，数字，赛车)，但每个元素中的所有元素顺序均不变.

As you can see, the three elements below "Thing" are now sorted alphabetically (Letters,Numbers,Racecars) but all elements within each of these are unchanged in order.

请让我知道您是否想要写出SELECT ... FOR XML EXPLICIT来生成上面的示例XML；我会很乐意这样做-我只是希望答案会直接在上面的XML上以xquery的形式出现，所以认为它不太可能需要！

Please let me know if you would like me to write out a SELECT ... FOR XML EXPLICIT to generate the example XML above; I will gladly do so - I just expected that the answer would be in the form of an xquery directly on the XML above, so thought it unlikely to be needed!

推荐答案

您必须重构<Things/>元素，并按<Thing/>的名称排序.该代码段按第一个孩子的名字排序，因为我们需要一个单独的值来排序.关于给定的输入，似乎没有必要在<Thing/>内的 内进行排序(甚至可能是错误的).

You have to reconstruct the <Things/> elements, ordering by the <Thing/>'s name. This snippet orders by the first child's name, as we need a single value to sort after. Regarding the input given, it does not seem necessary to order inside the <Thing/> (and might even be wrong).

element Things {
  for $thing in /Things/Thing
  let $name := local-name(($thing/*)[1])
  order by $name
  return $thing
}

这篇关于修改XML以按名称对中级复杂类型元素进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！