SQL Server 2005 select for XML path with union in sub-selection problem
I'm rather experienced with SQL server "select for XML path" queries but now i run into a strange problem.
The following query works fine:
select
(
select
'Keyfield1' as "@Name",
t1.Keyfield1 as "Value"
from MyTable t1
where
t1.KeyField1= t2.KeyField1 and
t1.KeyField2= t2.KeyField2
for xml path('Field'),type, elements
) as 'Key'
from MyTable t2
for XML path('Path') , elements XSINIL, root('Root')
This will result (for a dummy dataset) in this XML:
<Root>
<Path>
<Key Name="KeyField1">
<Value>DummyValue1</Value>
</Key>
</Path>
</Root>
In my result of this (part of a bigger) statement i nee开发者_如何学God the 2nd keyfield too:
<Root>
<Path>
<Key Name="KeyField1">
<Value>DummyValue1</Value>
</Key>
<Key Name="KeyField2">
<Value>DummyValue2</Value>
</Key>
</Path>
</Root>
So i changed my (sub)query with a union-select to:
select
(
select
'Keyfield1' as "@Name",
t1.Keyfield1 as "Value"
union all
select
'Keyfield2' as "@Name",
t1.Keyfield2 as "Value"
from MyTable t1
where
t1.KeyField1= t2.KeyField1 and
t1.KeyField2= t2.KeyField2
for xml path('Field'),type, elements
) as 'Key'
from MyTable t2
for XML path('Path') , elements XSINIL, root('Root')
But now i get the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
I know it is possible to have multiple records in a subquery with for XML path witch results in multiple elements. But i don't understand why this can't be done with a union.
Can someone put me in the right direction how to accomplisch the XML with the 2 keyfields in my (sub)query?
Thanx you very much.
The problem with your subselect is that the first part isn't referring to any table at all (no FROM-
clause).
This listing gives me the output you requested:
declare @mytable table (
keyfield1 nvarchar(20),
keyfield2 nvarchar(20)
)
insert into @mytable values ('Dummyvalue1', 'Dummyvalue2')
select * from @mytable
select
(
select
'Keyfield1' as "@Name",
t1.Keyfield1 as "Value"
from @mytable t1
where
t1.KeyField1= t2.KeyField1 and
t1.KeyField2= t2.KeyField2
for xml path('Field'),type, elements
) as 'Key'
from @mytable t2
for XML path('Path') , elements XSINIL, root('Root')
select
(
select * from (
select
'Keyfield1' as "@Name",
t1.Keyfield1 as "Value"
from @MyTable t1
where
t1.KeyField1= t2.KeyField1
union all
select
'Keyfield2' as "@Name",
t3.Keyfield2 as "Value"
from @MyTable t3
where
t3.KeyField2= t2.KeyField2) a
for xml path('Field'),type, elements
) as 'Key'
from @MyTable t2
for XML path('Path') , elements XSINIL, root('Root')
Here is a simplified example, but does this get you what you need?
select
(
select
'Keyfield1' as "@Name",
'Blah' as "Value"
for xml path('Key'),type, elements
),
(
select
'Keyfield2' as "@Name",
'Blah' as "Value"
for xml path('Key'),type, elements
)
for XML path('Path') , elements XSINIL, root('Root')
精彩评论