I am trying to generate this format of xml and having difficulties because of
the properties section. I can have unlimited number of properties and "For XML" tells me it can't generate the same node "Properties/@Name". I have tried several methods online, but can't seem to get it to work.
OptionLists>
<OptionList Name="Functions">
<Option Value="L9496">
<Properties Name="Description" Value="L9496 - Privacy Lock With Indicator" />
<Properties Name="Locked" Value="False" />
</Option>
<Option Value="LV9040" />
<Option Value="L9485" />
</OptionList>
</OptionLists>
My SQL currently looks like this:
SELECT
'Functions' as '@Name',
(
Select
coalesce(nullif(No_,'),'NONE') "Option/@Value",
Case NO_ when isNull(nullif(NO_,'),null) Then 1 Else 99 END "Option/@Order",
'TRUE' "Option/@Visible",
'FALSE' "Option/@Locked",
' "Option/@ToolTip",
' "Option/@ImageLink",
' "Option/@InfoMessage",
'QtySensitive' "Option/Properties/@Name",
Case [Quantity Sensitive] When 0 Then 'False' When 1 Then 'True' End "Option/Propert
View Complete Post