Help with this FOR XML EXPLICIT statement

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 2000 Forums

Transact-SQL (2000)

Help with this FOR XML EXPLICIT statement

Author

Topic

rhaazy
Starting Member

3 Posts

Posted - 2006-07-27 : 10:58:19

this is the XML I WANT:

<?xml version="1.0" encoding="utf-8" ?>
- <HelloRequest>
- <Profile ID="1">
<Configuration ID="1" PID="1" Name="ScanElapsedTime" UOM="Second">200</Configuration>
<Configuration ID="2" PID="1" Name="LastScanDate" UOM="Time">XXXXX</Configuration>
<Configuration ID="3" PID="1" Name="ClientID" UOM="Value">XXXXX</Configuration>
<Configuration ID="4" PID="1" Name="BO" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="5" PID="1" Name="Drives" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="6" PID="1" Name="ErrorLog" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="7" PID="1" Name="InstalledSoftware" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="8" PID="1" Name="Memory" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="9" PID="1" Name="OS" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="10" PID="1" Name="PhysicalDisks" UOM="IsScanned">TRUE</Configuration>
<Configuration ID="11" PID="1" Name="Processor" UOM="IsScanned">FALSE</Configuration>
<Configuration ID="12" PID="1" Name="RunningProgs" UOM="IsScanned">FALSE</Configuration>
<Configuration ID="13" PID="1" Name="Security" UOM="IsScanned">FALSE</Configuration>
<Configuration ID="14" PID="1" Name="Services" UOM="IsScanned">FALSE</Configuration>
<Configuration ID="15" PID="1" Name="StartUp" UOM="IsScanned">FALSE</Configuration>
<Configuration ID="16" PID="1" Name="TCPIP" UOM="IsScanned">FALSE</Configuration>
<Configuration ID="17" PID="1" Name="InternetCache" UOM="IsScanned">FALSE</Configuration>
</Profile>
</HelloRequest>

This is the XML I am getting right now :

<?xml version="1.0" encoding="utf-8" ?>
- <HelloRequest>
- <Profile ID="1">
<Configuration ID="1" PID="1" Name="ScanElapsedTime" UOM="Second" Value="200" />
<Configuration ID="2" PID="1" Name="LastScanDate" UOM="Time" Value="xxxxx" />
<Configuration ID="3" PID="1" Name="ClientID" UOM="Value" Value="xxxxx" />
<Configuration ID="4" PID="1" Name="BO" UOM="IsScanned" Value="TRUE" />
<Configuration ID="5" PID="1" Name="Drives" UOM="IsScanned" Value="TRUE" />
<Configuration ID="6" PID="1" Name="ErrorLog" UOM="IsScanned" Value="TRUE" />
<Configuration ID="7" PID="1" Name="InstalledSoftware" UOM="IsScanned" Value="TRUE" />
<Configuration ID="8" PID="1" Name="Memory" UOM="IsScanned" Value="TRUE" />
<Configuration ID="9" PID="1" Name="OS" UOM="IsScanned" Value="TRUE" />
<Configuration ID="10" PID="1" Name="PhysicalDisks" UOM="IsScanned" Value="TRUE" />
<Configuration ID="11" PID="1" Name="Processor" UOM="IsScanned" Value="FALSE" />
<Configuration ID="12" PID="1" Name="RunningProgs" UOM="IsScanned" Value="FALSE" />
<Configuration ID="13" PID="1" Name="Security" UOM="IsScanned" Value="FALSE" />
<Configuration ID="14" PID="1" Name="Services" UOM="IsScanned" Value="FALSE" />
<Configuration ID="15" PID="1" Name="StartUp" UOM="IsScanned" Value="FALSE" />
<Configuration ID="16" PID="1" Name="TCPIP" UOM="IsScanned" Value="FALSE" />
<Configuration ID="17" PID="1" Name="InternetCache" UOM="IsScanned" Value="FALSE" />
</Profile>
</HelloRequest>

Hopefully you can notice the difference....anyway here is the statement that gives me what I have right now:

select 1 as Tag, 0 as Parent, ID as [Configuration!1!ID], PID as [Configuration!1!PID], Name as [Configuration!1!Name], UOM as [Configuration!1!UOM], Value as [Configuration!1!Value] from tblProfile1 where MessageID = '1' for xml explicit

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-27 : 12:38:49

Try...

select 1 as Tag, 0 as Parent, ID as [Configuration!1!ID], PID as [Configuration!1!PID], Name as [Configuration!1!Name], UOM as [Configuration!1!UOM], Value as [Configuration!1~~!Value~~] from tblProfile1 where MessageID = '1' for xml explicit

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rhaazy
Starting Member

3 Posts

Posted - 2006-07-31 : 10:18:40

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources