5 replies to this topic

[hoser2001]

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

[Void]

I see your select statement but what actually creates the XML file?

[hoser2001]

Void said:

I see your select statement but what actually creates the XML file?

// Get the connection string
string str_connection = "Provider=sqloledb;user id=csTest;password=******;initial catalog=ClientScanTest;data " +
"source=SERVER3;connect timeout=30";
// Create a new SqlXmlCommand object
SqlXmlCommand xmlCmd = new SqlXmlCommand(str_connection);

// Set the text of the command
xmlCmd.CommandText = @"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";

// Execute the command to retrieve the xml from sql in the form of stream
Stream strmObject = xmlCmd.ExecuteStream();
// Set the position of the stream object to 0
strmObject.Position = 0;
// Add root tag
string encoding = "<?xml version=\"1.0\" encoding=\"utf-8\"?>";
string strResult = encoding + "<HelloRequest><Profile ID=\"1\">";
// Assign the stream to the StreamReader
using(StreamReader srdrObject = new StreamReader(strmObject))
{
strResult += srdrObject.ReadToEnd();
}
// Close root tag
strResult += "</Profile></HelloRequest>";

XmlDocument doc = new XmlDocument();
doc.LoadXml(strResult);
listBox1.Items.Add(strResult);

doc.Save("test1.xml");

[hoser2001]

This problem is solved, thanks for everyone's help anyway.

[RobSoftware]

What was the solution?

[hoser2001]

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

The text in red is what was needed to be removed in order for my query to work correclty.