quote:

---

Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \n (newline character) as the row terminator.

---

<thetable><col1>abc</col1><col2>1</col2></thetable><thetable><col1>def</col1><col2>2</col2></thetable>

<thetable>
<col1>abc</col1>
<col2>1</col2>
</thetable>
<thetable>
<col1>def</col1>
<col2>2</col2>
</thetable>

quote:

---

Originally posted by jezemine

if you write the xml using XmlWriter, you can use the XmlWriterSettings class to specify how you want the indenting and linebreaks to work. See the Indent, IndentChars, NewLineChars properties on that class.

to use this class means you have to consume your stream by a .net app though. perhaps you are already doing so?

---

www.elsasoft.org

---

XmlWriter xw = XmlWriter.Create(...);
SqlConnection = new SqlConnection("...");
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT col1,col2 FROM tbl";
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())
{
	for(i=0;i<dr.FieldCount;i++)
	xw.WriteElementString(dr.GetName(i),dr[i].ToString());
}

cn.Close();

public void rockmoose()
{
    XmlWriterSettings s = new XmlWriterSettings();
    s.Indent = true;
    s.Encoding = Encoding.Unicode;
    s.OmitXmlDeclaration = false;

    using (XmlWriter xw = XmlWriter.Create("C:\\foo.xml", s))
    using (SqlConnection cn = new SqlConnection("..."))
    using (SqlCommand cmd = cn.CreateCommand())
    {
        cmd.CommandText = "SELECT col1,col2 FROM tbl";
        cn.Open();
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            int fields = dr.FieldCount;
            xw.WriteStartElement("root");
            while (dr.Read())
            {
                xw.WriteStartElement("rockmoose");
                for (int i = 0; i < fields; i++)
                {
                    if (dr.IsDBNull(i)) continue;

                    xw.WriteAttributeString(dr.GetName(i), dr[i].ToString());
                }
                xw.WriteEndElement();
            }
            xw.WriteEndElement();
        }
    }
}

## sample call
## .\exportXml "SELECT TOP 10 * FROM master.dbo.sysobjects" "C:\Dev\ps\" "sys.objects"

param($select,$path,$file,$instance)

if($select -eq $null -or $path  -eq $null -or $file -eq $null)
{
"usage:
.\x `"SELECT FROM clause`" `"x:\path\`" `"filename`"";
exit;
}

## get the server name if instance not provided, defaults to local machine
if($instance -eq $null){ $srv = (gwmi Win32_ComputerSystem | Select-Object "Name").Name; }

## initialize variables and resources
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=" + $instance + ";Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.Data.Sqlclient.SqlCommand($select, $cn);
$cmd.CommandType = [System.Data.CommandType]"Text"


$s = new-object System.Xml.XmlWriterSettings;
$s.Indent = $true;
$s.Encoding = [System.Text.Encoding]::Unicode;
$xw = [System.Xml.XmlWriter]::Create($path+$file+".xml",$s);


## cleanup resources
function cleanup()
{
	if($cn -ne $null){$cn.Close();}
	if($xw -ne $null){$xw.Close();}
}

## generic error trap
trap
{
	$error[0];
	cleanup;
	exit;
}


## do the work
$xw.WriteStartElement($file);

$cn.Open()
$rdr = $cmd.ExecuteReader();
while($rdr.Read())
{
	$xw.WriteStartElement("row");
	$j = $rdr.FieldCount;
	for($i=0;$i -lt $j;$i++)
	{
		$val = "";
		if(!$rdr[$i].IsDBNull){ $val = $rdr[$i].ToString(); }
		$xw.WriteElementString($rdr.GetName($i),$val);
		#$rdr.GetName($i);$rdr[$i];
	}
	$xw.WriteEndElement();
}
$xw.WriteEndElement();

## create a file with the schema
$rdr.GetSchemaTable().WriteXml($path+$file+"schema.xml");


cleanup;


## To view the file and the schema file, uncomment
# "SCHEMA FILE: " + ($path+$file+"schema.xml"); Get-Content ($path+$file+"schema.xml");
# "DATA FILE: " + ($path+$file+"schema.xml"); Get-Content ($path+$file+".xml");

Exception calling "WriteElementString" with "2" argument(s): "Invalid name character in 'spa ce'. The ' ' character, hexadecimal value 0x20, cannot be included in a name."
At C:\dev\ps\exportXml.ps1:57 char:25
+         $xw.WriteElementString( <<<< $rdr.GetName($i),$val);

quote:

---

Originally posted by jezemine

exactly, that's what i meant by "break".

---

www.elsasoft.org

---