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)  XML data file question (select and looping)

Author  Topic  mabr0406 Starting Member 1 Post Posted - 2009-03-17 : 12:13:27 Hello. I am new to SQL and have a question about XML, select, and looping. So far I have written code that extracts the data from my table and sucessfully writes one segment of XML data, unfortunately, I believe I need a loop in order to get all my data to come from my table. Here is my code so far: --RSLogix5000Content Level select SchemaRevision, SoftwareRevision, TargetName Name, TargetType, ContainsContext, [Owner], ExportDate, --RSLogix5000Content/Controller Level (SELECT [Use], ControllerName Name, ProcessorType, MajorRev, MinorRev, TimeSlice, ShareUnusedTimeSlice, ProjectCreationDate, LastModifiedDate, SFCExecutionControl, SFCRestartPosition, SFCLastScan, CommPath, ProjectSN, MatchProjectToController, InhibitAutomaticFirmwareUpdate, --RSLogix5000Content/Controller/DataTypes Level (select DataTypeName Name, Family, Class, --RSLogix5000Content/Controller/DataTypes/Members Level (select MemberName Name, DataType, Dimension, Radix, Hidden, --RSLogix5000Content/Controller/DataTypes/Members/Description Level (select [Description] from XmlExtract [Description] Where MemberName = 'ZZZZZZZZZZButton1' FOR XML raw(''), elements, type) From XmlExtract Member Where MemberName = 'ZZZZZZZZZZButton1' FOR XML auto, type, root('Members')) From XmlExtract DataType WHERE MemberName = 'ZZZZZZZZZZButton1' FOR XML auto, type, root('DataTypes')) FROM XmlExtract Controller WHERE MemberName = 'ZZZZZZZZZZButton1' FOR XML AUTO, type) From XmlExtract RSLogix5000Content Where MemberName = 'ZZZZZZZZZZButton1' FOR XML auto, type Which creates this: <RSLogix5000Content SchemaRevision="1.0" SoftwareRevision="17.00" Name="IM3" TargetType="Controller" ContainsContext="false" Owner="Brandon, Relco" ExportDate="Thu Mar 12 10:16:07 2009" ExportOptions="DecoratedData AllProjDocTrans"> <Controller Use="Target" Name="IM3" ProcessorType="1756-L63" MajorRev="17" MinorRev="2" TimeSlice="20" ShareUnusedTimeSlice="1" ProjectCreationDate="Fri Oct 24 17:04:18 2008" LastModifiedDate="Thu Mar 05 20:52:54 2009" SFCExecutionControl="CurrentActive" SFCRestartPosition="MostRecent" SFCLastScan="DontScan" CommPath="AB_ETH-1\192.168.200.21\Backplane\0" ProjectSN="16#005a_ee49" MatchProjectToController="false" InhibitAutomaticFirmwareUpdate="0"> <DataTypes> <DataType Name="Button" Family="NoFamily" Class="User"> <Members> <Member Name="ZZZZZZZZZZButton1" DataType="SINT" Dimension="0" Radix="Decimal" Hidden="true"> <Description>I'm a balla</Description> </Member> </Members> </DataType> </DataTypes> </Controller> </RSLogix5000Content> However, I have multiple Members that have to be added to my code, and I would like to loop through my table in order to get these values. Or is there another way? Thanks for any help you can give me.