select XML data from parent child tables sql server 2008





Recently I was working on to create XML menu from SQL table. We have two tables one table is NavigationSystem and second table is Navigation Item. Following was required to show Parent Child relation ship.

 

<Navigation>
<NavigationSystem>
<systemid>1</systemid>
<Title>Top Menu</Title>
<Items>
<Item>
<systemid>1</systemid>
<ItemId>1</ItemId>
<label>Home</label>
<Url>http://www.isolutionteam.co.uk</Url>
</Item>
<Item>
<systemid>1</systemid>
<ItemId>2</ItemId>
<label>Database</label>
<Url>http://www.isolutionteam.co.uk/Database</Url>
</Item>
</Items
</NavigationSystem>
</Navigation>

 

Following SQL Script creates the above XML Navigation System for me.  If we do not use ISNULL() then if column values are null it will not create respective XML element. ISNULL therefore required to get respective XML element for the field name.

First I declared a XML type variable.

Second put XML values into  variable

Third get values from XML variable.

I tried to get values in text format and into a file using management studio but result text was not well formatted in bot cases. putting into XML type variable its well formatted xml .

declare @menus xml

SET @menus= (SELECT (SELECT [id]
,[title]
— child rows starts here
, (SELECT
[id]
,[NavigationSystemID]
,ISNULL([sLabel] ,”) as [sLabel]
,ISNULL([sURL] ,”) as [sURL]

FROM [dbo].[NavigationItems]
WHERE [dbo].[NavigationSystems].[NavigationSystemID]= [dbo].[NavigationItems].[NavigationSystemID]
FOR XML PATH(‘Item’), TYPE) as ‘Items‘ — child rows end here
FROM [dbo].[NavigationSystems]
FOR XML PATH(‘NavigationSystem’), TYPE)
FOR XML PATH(”),ROOT(‘Navigation’))
select @menus




Tags: ,

Comments are closed.