FOR XML multiple control by attribute in tree concept

Question

I want to figure out one issue.

I already had question about simple ordering issue but I want to order more detail. check below this link : SQL Server : FOR XML sorting control by attribute

I made a example case.

SQL Query.

select (
    select '123' AS '@id', ( 
        select 
        (
            select 'test' AS '@testid' , '20' AS '@order'
            FOR XML path ('tree') , TYPE
        ),
        (
            select 'test2' AS '@testid' , '30' AS '@order'
            FOR XML path ('tree-order') , TYPE
        ),
        (
            select 'test' AS '@testid' , '10' AS '@order'
            FOR XML path ('tree') , TYPE
        )
        FOR XML path ('Node') , TYPE
    )
    FOR XML path ('Sample') , TYPE
    ),
    (select '456' AS '@id', ( 
        select 
        (
            select 'test' AS '@testid' , '20' AS '@order'
            FOR XML path ('tree') , TYPE
        ),
        (
            select 'test2' AS '@testid' , '30' AS '@order'
            FOR XML path ('tree-order') , TYPE
        ),
        (
            select 'test' AS '@testid' , '10' AS '@order'
            FOR XML path ('tree') , TYPE
        )
        FOR XML path ('Node') , TYPE
    )
    FOR XML path ('Sample') , TYPE)
FOR XML path ('Main') , TYPE

Result :

<Main>
  <Sample id="123">
    <Node>
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
      <tree testid="test" order="10" />
    </Node>
  </Sample>
  <Sample id="456">
    <Node>
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
      <tree testid="test" order="10" />
    </Node>
  </Sample>
</Main>

Expected result :

<Main>
  <Sample id="123">
    <Node>
      <tree testid="test" order="10" />
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
    </Node>
  </Sample>
  <Sample id="456">
    <Node>
      <tree testid="test" order="10" />
      <tree testid="test" order="20" />
      <tree-order testid="test2" order="30" />
    </Node>
  </Sample>
</Main>

final result :

<Main>
  <Sample id="123">
    <Node>
      <tree testid="test" />
      <tree testid="test" />
      <tree-order testid="test2" />
    </Node>
  </Sample>
  <Sample id="456">
    <Node>
      <tree testid="test" />
      <tree testid="test" />
      <tree-order testid="test2" />
    </Node>
  </Sample>
</Main>

That's order by tree-order.

finally I don't want to show order information in attribute

Any one has great Idea?

Thank you for everybody who interesting to this.

Updated ----------------------------------------

Thank you every body finally I solved problem as below about order by and remove attribute issue :

declare @resultData xml = (select @data.query('
  element Main {
    for $s in Main/Sample
    return element Sample {
      $s/@*,
      for $n in $s/Node
      return element Node {
        for $i in $n/* 
        order by $i/@order
        return $i 
      }
    }  
  }'));

  SET @resultData.modify('delete (Main/Sample/Node/tree/@order)');
  SET @resultData.modify('delete (Main/Sample/Node/tree-order/@order)');

  select @resultData

wst · Accepted Answer · 2013-12-18T20:37:30.120

2

select @data.query('
  element Main {
    for $s in Main/Sample
    return element Sample {
      $s/@*,
      for $n in $s/Node
      return element Node {
        for $i in Node/* 
        order by $i/@order
        return 
          if ($i/self::tree)
          then element tree { $i/@testid }
          else element tree-order { $i/@testid }
        }
      }
    }  
  }')

edited Dec 18 '13 at 20:37

answered Dec 18 '13 at 17:03

wst

11,681
1
24
39

That's amazing almost solved problem, thank you. I am not really familiar with XQuery. Almost solve problem without remove attribute part. what is "node-name($I)" part ? – clear.choi Dec 18 '13 at 19:07
@clear.choi `node-name()` returns the element's `xs:QName` value. This bit of code allows you to reconstruct elements of different names (like `` and ``) using the same rules for their children. Also, this code should exclude the `@order` attribute: `$i/@* except $i/@order`. What problem are you having with the output? – wst Dec 18 '13 at 19:18
@clear.choi Just updated a bug in the code. `` children should be ordered correctly now. – wst Dec 18 '13 at 19:20
just get error message like XQuery [query()]: Only constant expressions are supported for the name expression of computed element and attribute constructors. and just try how to used it :) – clear.choi Dec 18 '13 at 19:23
ordered was ok ~ just problem on remove attribute part – clear.choi Dec 18 '13 at 19:36
@clear.choi A few ideas: 1) try replacing `node-name($i)` with `local-name($i)`, 2) try replacing `$i/@* except $i/@order` with `$i/@testid`, 3) remove `$i/node()`. – wst Dec 18 '13 at 19:55
Thank you for your answer, u mean return element { local-name($i) } { $i/@order } like this? still have same error... – clear.choi Dec 18 '13 at 20:09
@clear.choi No, don't output `@order`. Use `return element { local-name($i) } { $i/@testid }`. When you say error, do you mean incorrect output, or that you're actually getting an error? – wst Dec 18 '13 at 20:24
Yeeeeh it's actual error like "XQuery [query()]: Only constant expressions are supported for the name expression of computed element and attribute constructors." same error as before – clear.choi Dec 18 '13 at 20:30
@clear.choi Ugh, the problem is that SQL Server doesn't support dynamically constructed element names. See updated answer for solution. – wst Dec 18 '13 at 20:38
Thank you for your answer I solved issue already, I will update in main screen – clear.choi Dec 18 '13 at 21:19

score 2 · Answer 2 · edited May 23 '17 at 12:11

2

What's interesting to me is that in your original post, you're stating that you're generating the XML as the result of a SQL query. If it were me, I'd control the ordering at that level.

edited May 23 '17 at 12:11

Community

1
1

answered Dec 18 '13 at 20:07

Ben Thul

31,080
4
45
68

Thank you for your answer I already solved order control issues just remaining remove attribute after finished ordering :) – clear.choi Dec 18 '13 at 20:14
1

My point though is that if you solve the ordering issue another way, you get to solve this issue for free. – Ben Thul Dec 18 '13 at 20:51
ok thank you for your advice finally I solved remove attribute issues by my self :) – clear.choi Dec 18 '13 at 21:27

FOR XML multiple control by attribute in tree concept

2 Answers2

Linked