Some pain with FOR XML EXPLICIT

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)

Some pain with FOR XML EXPLICIT

Author

Topic

ciaranh
Starting Member

1 Post

Posted - 2009-03-04 : 07:19:38

Hey,
am having some pain with FOR XML EXPLICIT and getting xml properly formatted. Hopefully some one has done this frequently before. Have the two tables below and the sample xml

-----table A-------
CREATE TABLE dbo.FeedInfo(
FeedId smallint IDENTITY(1,1) ,
FeedName varchar(50) ,
Location nvarchar(250) ,
codepage smallint ,
commandTimeout smallint ,
IsActive bit
)

-- table B--
CREATE TABLE dbo.FeedSetting(
FeedSettingId smallint IDENTITY(1,1) ,
FeedId smallint ,
key nvarchar(50) ,
value nvarchar(250) ,
IsActive bit ,
)

--- xml file --

<ArrayOfFeedInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<FeedInfo FeedName="WMFeed"
Location="D:\ImpactFileProcessor\File Destination\"
CodePage="1252"
CommandTimeout="180">
<Settings>
<FeedSetting Key="Proc_ReadMloadData" Value="test"/>
<FeedSetting Key="Proc_ErrorInsertProcedure" Value="test" />
<FeedSetting Key="MloadDumpPath" Value="bigears"/>
<FeedSetting Key="MloadArchivePath" Value="some"/>
<FeedSetting Key="ActionSourcePath" Value="another"/>
<FeedSetting Key="MloadFileHeader" Value="WMFeed"/>
</Settings>
</FeedInfo>

<FeedInfo FeedName="testFeed"
Location="D:\ImpactFileProcessor\File Destination\"
CodePage="1252"
CommandTimeout="180">
<Settings>
<FeedSetting Key="Proc_ReadMloadData" Value="test"/>
<FeedSetting Key="Proc_ErrorInsertProcedure" Value="test" />
<FeedSetting Key="MloadDumpPath" Value="bigears"/>
<FeedSetting Key="MloadArchivePath" Value="some"/>
<FeedSetting Key="ActionSourcePath" Value="another"/>
<FeedSetting Key="MloadFileHeader" Value="WMFeed"/>
</Settings>
</FeedInfo>
<ArrayOfFeedInfo

---- my attempt at the query. The two tables are related through feedId

My query doesnt seem to be nesting correctly

select 1 as Tag,
NULL as Parent,
FeedId as [FeedInfo!1!FeedId!hide],
FeedName as [FeedInfo!1!FeedName],
null as [FeedSetting!2!Settings!element!hide],
null as [FeedSetting!2!FeedSettings!element!hide],
null as [FeedSettings!3!key],
null as [FeedSettings!3!value]
FROM FeedInfo
UNION ALL
select 2 as Tag,
1 as Parent,
null,
null,
FeedSettingId,
FeedSetting.FeedId,
null,
null
from
Feedsetting Join FeedInfo on FeedSetting.FeedId = FeedInfo.FeedId
FOR XML EXPLICIT

Any Ideas

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-04 : 08:27:59

Add an order by clause, something like this:

Feedsetting Join FeedInfo on FeedSetting.FeedId = FeedInfo.FeedId
order by
  [FeedInfo!1!FeedName], 
  [FeedSetting!2!Settings!element!hide]
  etc...
FOR XML EXPLICIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources