xquery join from SQL Select 'For XML'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

xquery join from SQL Select 'For XML'

Author

Topic

bhtownend
Starting Member

2 Posts

Posted - 2008-12-22 : 08:58:43

Hi
I am just starting to work with "FOR XML" queries. I have the following SQL statement in MSSQL 2005 and am unable to get any data in to the Item2 node of the XML.

SELECT (
SELECT Table1.FieldX, Table2.FieldY
FROM Table1 Left JOIN
Table2 ON Table1.ID = Table2.ID
WHERE Table1.FieldZ='abc'
for xml auto, type).query (
'<xmlDemo>
{for $sp in /*
return
<Items>
<Item1>{data($sp/@FieldX)}</Item1>
<Item2>{data($sp/@FieldY)}</Item2>
</Items>}
</xmlDemo>')

So far I have tried "{for $sp in /FieldX, or $sp in /FieldY" , selecting the entire Select (..) as _derived with {for $sp in /_derived
all to no avail. I have also attempted to create an XQuery join but I am not sure the syntax was correct.
I know it must be something to do with joins but I am not sure where to go.
Any help would be much appreciated.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 09:02:08

Some sample data and expected output would be nice.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 09:07:52

As all XML, it is case sensitive.

DECLARE	@Sample TABLE
	(
		fieldX INT,
		fieldY INT
	)

INSERT	@Sample
SELECT	13, 19

SELECT (
SELECT fieldX, fieldY
FROM @Sample
for xml auto, type).query (
'<xmlDemo>
{for $sp in /*
return
<Items> 
<Item1>{data($sp/@fieldX)}</Item1>
<Item2>{data($sp/@fieldY)}</Item2> 
</Items>}
</xmlDemo>')

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 09:14:27

Above query will work.

This query will only work for fieldY, since fieldX does not have same case sensitivity.

SELECT (
SELECT fieldX, fieldY
FROM @Sample
for xml auto, type).query (
'<xmlDemo>
{for $sp in /*
return
<Items> 
<Item1>{data($sp/@FieldX)}</Item1>
<Item2>{data($sp/@fieldY)}</Item2> 
</Items>}
</xmlDemo>')

E 12°55'05.63"
N 56°04'39.26"

bhtownend
Starting Member

2 Posts

Posted - 2008-12-22 : 09:28:16

Fantastic. Thank you. It works perfectly. I was putting some sample data together but you came up with the solution before I got there! XML now reads:
<xmlDemo>
<Items>
<Item1>Hello</Item1>
<Item2>World</Item2>
</Items>
</xmlDemo>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 09:35:22

You can also use this directly without having to resort to advanced querying.

SELECT	fieldX AS Item1,
	fieldY AS Item2
FROM	@Sample
FOR XML	PATH('Items'),
	ROOT('xmlDemo'),
	ELEMENTS

or in your case, just

SELECT		t1.FieldX AS Item1,
		t2.FieldY AS Item2
FROM		Table1 AS t1
LEFT JOIN	Table2 AS t2 ON t2.ID = t1.ID 
WHERE		t1.FieldZ = 'abc'
FOR XML		PATH('Items'),
		ROOT('xmlDemo'),
		ELEMENTS

Looks a lot cleaner, doesn't it?
Instead of

SELECT (
SELECT Table1.FieldX, Table2.FieldY
FROM Table1 Left JOIN
Table2 ON Table1.ID = Table2.ID 
WHERE Table1.FieldZ='abc'
for xml auto, type).query (
'<xmlDemo>
{for $sp in /*
return
<Items> 
<Item1>{data($sp/@FieldX)}</Item1>
<Item2>{data($sp/@FieldY)}</Item2> 
</Items>}
</xmlDemo>')

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources