multiple joins for XML COLUMN - SQL Server Forums

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)

multiple joins for XML COLUMN

Author

Topic

Mindscape
Starting Member

5 Posts

Posted - 2007-04-24 : 11:01:13

Hi,
I have a table named AllData .The fields are shown below with data types
ID >>int
CATID >>int
TumBilgiler>>xml

as you see TumBilgiler is an XML column.
In TumBilgiler column my datas are like shown below
<restaurants>
<restaurant numara="6">
<isim>KONYALIM</isim>
<TEL>02127412585</TEL>
<SEMT>ADALAR</SEMT>
<ADRES>ISTANBUL</ADRES>
</restaurant>
</restaurants>

So that I get the data as a table with the code purple color below

SELECT
CatID,
a.value('isim[1]', 'varchar(50)') AS isim,
a.value('TEL[1]', ' varchar(50) ') AS Telefon,
a.value('SEMT[1]', ' varchar(50) ') AS SEMT,
a.value('ADRES[1]', ' varchar(50) ') AS ADRES
FROM
AllData
OUTER APPLY TumBilgiler.nodes('restaurants/restaurant') AS result(a)
WHERE
CatID = 1

there is no problem with the result.But I want to add one more node to the xml data like shown below with the color red

<restaurants>
<restaurant numara="6">
<isim>Restaurant Menekþe</isim>
<TEL>02122584512</TEL>
<SEMT>FATIH</SEMT>
<ADRES>ISTANBUL</ADRES>
<ADRES1>ISTANBUL</ADRES1>
<Konaklama>
<Garden>1</Garden>
<Terrace>1</Terrace>
<Balcony>0</Balcony>
</Konaklama>
</restaurant>
</restaurants>

So that I want to get thoose 3 of columns(Garden,Terrace,Balcony) to my result added as I the purple color procedure shown above.I coulnt succeed while trying to join as shown below.I just discovered xml column and working on it.The modification is shown with bold black color.

SELECT
CatID,
a.value('isim[1]', 'varchar(50)') AS isim,
a.value('TEL[1]', ' varchar(50) ') AS Telefon,
a.value('SEMT[1]', ' varchar(50) ') AS SEMT,
a.value('ADRES[1]', ' varchar(50) ') AS ADRES,
b.value('Garden[1]', 'int') AS GARDEN,
b.value('Terrace[1]', 'int') AS TERRACE,
b.value('Balcony[1]' ,'int') AS BALCONY
FROM
AllData
OUTER APPLY TumBilgiler.nodes('restaurants/restaurant') AS result(a)
OUTER APPLY TumBilgiler.nodes('restaurants/restaurant/Konaklama') AS result(b)
WHERE
CatID = 1

Thanks ...

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-24 : 19:22:41

quote:
Originally posted by Mindscape

OUTER APPLY TumBilgiler.nodes('restaurants/restaurant') AS result (a)
OUTER APPLY TumBilgiler.nodes('restaurants/restaurant/Konaklama') AS result(b)

Try changing the name given to the second resultset to something other than 'result'.

Mindscape
Starting Member

5 Posts

Posted - 2007-04-25 : 02:19:33

Works great now

, Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources