For XML Explicit SQL duplicating elements

Question

Post reply

For XML Explicit SQL duplicating elements

Jules Bonnot

SSCertifiable

Points: 5488
More actions
February 3, 2011 at 7:35 am

#388772

I need two elements both to appear as children of the root. They must be in alphabetical order so elementA must be before elementZ. ElementZ should appear only once whereas elementA needs to appear many times. Any help must appreciated. This is greatly simplified part of a much bigger query that prevents 'path' being used so I need a solution using explicit.
Thanks a lot
declare @xml xml
DECLARE @tab table (
root_element nvarchar(10),
elementA nvarchar(10),
elementZ nvarchar(10)
)
insert @tab
(root_element, elementA, elementZ)
select 'one' , 'many', 'one' union all
select 'one' , 'many1', 'one' union all
select 'one' , 'many2', 'one' union all
select 'one' , 'many3', 'one' union all
select 'one' , 'many4', 'one' union all
select 'one' , 'many5', 'one'
SET @xml =(
SELECT DISTINCT
TAG,
Parent,
[root_element!1!value],
[elementA!2!value],
[elementZ!3!value]
FROM
(
SELECTDISTINCT
1AS TAG,
NULLAS Parent,
root_element AS [root_element!1!value],
NULL AS [elementA!2!value],
NULL AS [elementZ!3!value]
FROM @tab
UNION ALL
SELECTDISTINCT
2,
1,
root_element,
elementA,
NULL
FROM @tab
UNION ALL
SELECTDISTINCT
3,
1,
root_element,
elementA,
elementZ
FROM @tab
)a
ORDER BY
[root_element!1!value],
[elementA!2!value],
[elementZ!3!value]
FOR XML EXPLICIT
)
select @xml
--results in this
'<root_element value="one">
<elementA value="many" />
<elementZ value="one" />
<elementA value="many1" />
<elementZ value="one" />
<elementA value="many2" />
<elementZ value="one" />
<elementA value="many3" />
<elementZ value="one" />
<elementA value="many4" />
<elementZ value="one" />
<elementA value="many5" />
<elementZ value="one" />
</root_element>'
--but i want this.
'<root_element value="one">
<elementA value="many" />
<elementA value="many1" />
<elementA value="many2" />
<elementA value="many3" />
<elementA value="many4" />
<elementA value="many5" />
<elementZ value="one" />
</root_element>'
www.sql-library.com[/url]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

gsc_dba SSCertifiable Points: 5407 More actions · Answer 1

DECLARE @xml XML

DECLARE @tab TABLE

(

root_element NVARCHAR(10)

, elementA NVARCHAR(10)

, elementZ NVARCHAR(10)

)

INSERT @tab

( root_element

, elementA

, elementZ

)

SELECT 'one'

, 'many'

, 'one'

UNION ALL

SELECT 'one'

, 'many1'

, 'one'

UNION ALL

SELECT 'one'

, 'many2'

, 'one'

UNION ALL

SELECT 'one'

, 'many3'

, 'one'

UNION ALL

SELECT 'one'

, 'many4'

, 'one'

UNION ALL

SELECT 'one'

, 'many5'

, 'one'

SET @xml = ( SELECT DISTINCT

TAG

, Parent

, [root_element!1!value]

, [elementA!2!value]

, [elementZ!3!value]

FROM ( SELECT DISTINCT

1 AS TAG

, NULL AS Parent

, root_element AS [root_element!1!value]

, NULL AS [elementA!2!value]

, NULL AS [elementZ!3!value]

FROM @tab

UNION ALL

SELECT DISTINCT

2

, 1

, root_element

, elementA

, NULL

FROM @tab

UNION ALL

SELECT DISTINCT

3

, 1

, root_element

, elementA

, elementZ

FROM @tab

) a

--ORDER BY [root_element!1!value]

-- , [elementA!2!value]

-- , [elementZ!3!value]

FOR

XML EXPLICIT

)

SELECT * FROM @tab AS T

SELECT @xml

Try this - I have commented out your order by clause

gsc_dba

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

doesnt help returns this

<root_element value="one">

</root_element>

www.sql-library.com[/url]

gsc_dba SSCertifiable Points: 5407 More actions · Answer 3

and you want this?

<root_element value="one">

<elementA value="many" />

<elementA value="many1" />

<elementA value="many2" />

<elementA value="many3" />

<elementA value="many4" />

<elementA value="many5" />

<elementZ value="one" />

<elementZ value=NULL />

</root_element>

gsc_dba

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 4

Jules Bonnot

SSCertifiable

Points: 5488

February 3, 2011 at 10:30 am

#1282025

NO 😉

www.sql-library.com[/url]

gsc_dba SSCertifiable Points: 5407 More actions · Answer 5

Jules Bonnot (2/3/2011)
yes

Apologies - I had edited my comment - please see above 😀

I need to understand the problem more before I can offer a solution.

It seems to me you need to modify the content of the temporary table before you convert/insert as XML

gsc_dba

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 6

The interesting thing is that i can set the 'one' element at the top but I need the elements alphabetical order by name.

So this works

SET @xml = ( SELECT DISTINCT

TAG

, Parent

, [root_element!1!value]

, [elementZ!2!value]

, [elementA!3!value]

FROM ( SELECT DISTINCT

1 AS TAG

, NULL AS Parent

, root_element AS [root_element!1!value]

, NULL AS [elementZ!2!value]

, NULL AS [elementA!3!value]

FROM @tab

UNION ALL

SELECT DISTINCT

2

, 1

, root_element

, elementZ

, NULL

FROM @tab

UNION ALL

SELECT DISTINCT

3

, 1

, root_element

, elementZ

, elementA

FROM @tab

) a

--ORDER BY [root_element!1!value]

-- , [elementA!2!value]

-- , [elementZ!3!value]

FOR

XML EXPLICIT

)

'<root_element value="one">

</root_element>'

but I need the z element after the a element.

www.sql-library.com[/url]

gsc_dba SSCertifiable Points: 5407 More actions · Answer 7

I have tried various methods of ordering the result set, which eventually resulted in:

Msg 6833, Level 16, State 1, Line 39

Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set

Having googled the error:

http://sqlserverpedia.com/wiki/XML_-_Parent_Tags_Not_Open_Error

I think this may help you...?

Having explored using xquery:

SELECT @xml.query('for $A in /root_element

order by $A/elementA [1] ascending

return $A ') AS [flowr]

--Example ordering using XQUERY

DECLARE @Result XML

SET @Result = '<Result />'

SELECT @Result.query('

for $i in (1, 2, 3), $j in (3, 4, 5)

where $i < $j

order by sum($i + $j) descending

return sum($i + $j)

') AS Result

Ordering the values, rather than the column names which is what i think you want

gsc_dba

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Can I please ask why you need the output in a certain order?

XML is not normally used in that way (at least not in my experience) and I find it intriguing...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

gsc_dba SSCertifiable Points: 5407 More actions · Answer 9

mister.magoo (2/4/2011)
Can I please ask why you need the output in a certain order?
XML is not normally used in that way (at least not in my experience) and I find it intriguing...

I agree!

I thought I would take up the challenge as I have recently "zebra striped" an XHTML output using XQUERY...

gsc_dba

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 10

Yes, explicit ordering seems pointless but it is enforced by the xsd which validates it.

If there no way to sort it alphabetically then i need to remove the order enforcement from the xsd. Seems to be the <xsd:sequence> tag which is causing the problem. Any ideas how to make the schema order agnostic?

www.sql-library.com[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

Does this help at all?

;with upvt as

(

SELECT DISTINCT*

FROM @tab as [root_element]

unpivot (a for c in ([elementA],[elementZ])) upvt

)

select distinct 1 as tag,null as parent,root_element as [root_element!1!value],null as [elementA!2!value],null as [elementZ!3!value]

from upvt as root_element

union all

select distinct 2 as tag,1 as parent,root_element as [root_element!1!value],a as [elementA!2!value],null as [elementZ!3!value]

from upvt as root_element

where c='ElementA'

union all

select distinct 3 as tag,1 as parent,root_element as [root_element!1!value],null as [elementA!2!value],a as [elementZ!3!value]

from upvt as root_element

where c='ElementZ'

for xml explicit

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Another way to do it without EXPLICIT:

select t.root_element as '@value',

(select distinct elementA as 'elementA/@value'

from @tab tabinner1 where tabinner1.root_element=t.root_element

for XML path(''),TYPE),

(select distinct elementZ as 'elementZ/@value'

from @tab tabinner where tabinner.root_element=t.root_element

for XML path(''),TYPE)

from (select distinct root_element from @tab) t

for xml path('root_element')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?