Using XQuery with order by all elements and attribute

Question

I still have a problem about sorting xml by XQuery.

Please check below code.

As is :

<Main name = "test">
    <Sample id="1">
      <cal>
        <tree abc="123"/>
        <tree abc="789/>
        <tree-order abc="456/>
      </cal>
    </Sample>

     <Sample id="2">
      <cal>
        <tree abc="123"/>
        <tree abc="789/>
        <tree-order abc="456/>
      </cal>
    </Sample>

    <Sample id="3">
      <cal>
        <tree abc="123"/>
        <tree abc="789/>
        <tree-order abc="456/>
      </cal>
    </Sample>

</Main>

I want to order by attribute "abc"

To be 1:

<Main name = "test">
    <Sample id="1">
      <cal>
        <tree abc="123"/>
        <tree-order abc="456/>
        <tree abc="789/>
      </cal>
    </Sample>

     <Sample id="2">
      <cal>
        <tree abc="123"/>
        <tree-order abc="456/>
        <tree abc="789/>
      </cal>
    </Sample>

    <Sample id="3">
      <cal>
        <tree abc="123"/>
        <tree-order abc="456/>
        <tree abc="789/>
      </cal>
    </Sample>
</Main>

after that is it possible to remove attribute??

Final.

<Main name = "test">
    <Sample id="1">
      <cal>
        <tree />
        <tree-order />
        <tree />
      </cal>
    </Sample>

     <Sample id="2">
      <cal>
        <tree />
        <tree-order />
        <tree />
      </cal>
    </Sample>

    <Sample id="3">
      <cal>
        <tree />
        <tree-order />
        <tree />
      </cal>
    </Sample>
</Main>

like this.

so attribute abc is only for sorting.

I tried to like this

select @data.query('for $j in * order by number($j/@abc) return $j ')

then it's will show xml format without sorting.

Is there any way to solve this problem?

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Dec 18 '13 at 15:39
possible duplicate of [SQL Server : FOR XML sorting control by attribute](http://stackoverflow.com/questions/20613146/sql-server-for-xml-sorting-control-by-attribute) — wst, Dec 18 '13 at 16:58
That was my question, it's not duplicated it seems like cannot sort with Many tree structure — clear.choi, Dec 18 '13 at 17:02

wst · Accepted Answer · 2013-12-17T16:42:39.767

0

Process the XML recursively so that you can perform a sort on the <cal> element children and preserve its ancestor structure:

declare function local:sort(
  $xml as element(cal)
) as element()
{
  element cal {
    for $e in $xml/*
    order by $e/@abc
    return local:dispatch($e)
  }
};

declare function local:remove-atts(
  $xml as element()
) as element()
{
  element { node-name($xml) } {
    $xml/@* except $xml/@abc,
    $xml/node()
  }
};

declare function local:dispatch(
  $xml as element()
) as element()
{
  typeswitch ($xml)
    case element(cal) return local:sort($xml)
    case element(tree) return local:remove-atts($xml)
    case element(tree-order) return local:remove-atts($xml)
    default return local:process($xml)
};

declare function local:process(
  $xml as element()
) as element()
{
  element { node-name($xml) } {
    $xml/@*, 
    for $n in $xml/node()
    return local:dispatch($n)
  }
};

local:process($xml)

edited Dec 17 '13 at 16:42

answered Dec 17 '13 at 15:57

wst

11,681
1
24
39

1

Hello thanks your reply, That's a little bit confused. how can I get select result as select @data.query('for $j in * order by number($j/@abc) return $j ') like this @data function to get
tree
– clear.choi Dec 17 '13 at 16:13
Unfortunately the XQuery support in SQL Server is very limited, so it looks like you would need to create UDF wrappers for each of XQuery functions defined here. Then you could just call the `process` UDF. Hope that helps! – wst Dec 17 '13 at 16:18
1

I see but declare function is not work they said Incorret syntax near the keyword 'function' – clear.choi Dec 17 '13 at 20:58
1

I mean I am not sure how to use that function. first element
is already defined everything how can I bring xml information and to make function???? if follow your guidance, just element(cal) there is no connection between
xml.
– clear.choi Dec 17 '13 at 21:04
The connection is through `default`, which will pass through to `local:process`. This is how it's done using pure XQuery. Unfortunately it's much more complicated in SQL Server, and I don't know how to tell you to proceed. However, this would be even simpler in XSLT - have you considered that? – wst Dec 18 '13 at 01:02
Thank you for your answer :) I still couldn't find a way to solve this exactly... confused with tree structure \ – clear.choi Dec 18 '13 at 17:10
Answer is here http://stackoverflow.com/questions/20663908/for-xml-multiple-control-by-attribute-in-tree-concept – clear.choi Dec 18 '13 at 19:24

Using XQuery with order by all elements and attribute

1 Answers1