How to shred rows from XML in SQL server 2005

[okdeshpande]

Hi, I am trying to shred values of ModuleTorulemapid,selecteditemid and customvalue from following xml in SQL server 2005. For this purpose, I have written following code, but not able to shred it properly ---------------------------------------------------------------------- EXEC sp_xml_preparedocument @IDoc OUTPUT, @RollForwardRuleXML SELECT DISTINCT ModuleToRuleMapId 'ModuleToRuleMapGUID',SelectedItemId 'SelectedItemGUID',[UniqueIdentifier] 'OperationGUID', CustomValue FROM OPENXML(@IDOC,'/ROOT/RuleBuilderModule/RuleBuilderRule/RuleBuilderOperation /RuleBuilderItem ',2) WITH(ModuleToRuleMapId UNIQUEIDENTIFIER '/ROOT/RuleBuilderModule/RuleBuilderRule/@ModuleToRuleMapId', SelectedItemId UNIQUEIDENTIFIER '../@SelectedItemId', [UniqueIdentifier] UNIQUEIDENTIFIER '../@UniqueIdentifier', CustomValue NVARCHAR(50) '@CustomValue' ) EXEC sp_xml_removedocument @IDOC ----------------------------------------------------------------------- Following is the XML which I am using ----------------------------------------------------------------------- <ROOT> <RuleBuilderModule Name="RollForwardMethod" UniqueIdentifier="6d6800a6-9006-4d65-a579-731872b16004" Timestamp="0"> <RuleBuilderRule Expression="Which method, automatic or manual, do you want to use for roll forward? <38fc8c42-c1ba-4843-81a4-69d8b9b21b98>" LockedStatus="3" ModuleToRuleMapId="93d552ea-5d8b-4cbd-b936-030ea4fca60a" RuleType="Question" IsBubbleHelpRequired="False" UniqueIdentifier="b88e94f3-48dc-4ce8-ae0c-be17c1133486" Timestamp="0"> <RuleBuilderOperation Name="RBuildMthdAutoManual" ValueSource="Internal" DisplayStyle="Multiple" SelectedItemId="45d72595-c7b7-41e4-bea3-1ffefa9bbfae" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="38fc8c42-c1ba-4843-81a4-69d8b9b21b98" Timestamp="0"> <RuleBuilderItem Name="Automatic" IsDefault="True" CustomValue="" UniqueIdentifier="45d72595-c7b7-41e4-bea3-1ffefa9bbfae" Timestamp="0" /> <RuleBuilderItem Name="Manual" IsDefault="False" CustomValue="" UniqueIdentifier="b0e4538f-8dc0-46d0-925e-f4a52e0ffdc6" Timestamp="0" /> </RuleBuilderOperation> </RuleBuilderRule> <RuleBuilderRule Expression="Rule: Automatically roll the project <363afba8-df04-4af2-a661-dc458bb55f5d>" LockedStatus="3" ModuleToRuleMapId="d5aeeaa7-09d9-4b33-aff9-74cedef43814" RuleType="Rule" IsBubbleHelpRequired="True" UniqueIdentifier="6ee47614-4632-498e-a619-