SQL Server 2005 - Get XML Field to File; some xml fields to their own files
My string structure looks like ID bigint, ScanRept XML
I want to end up with a file named 4.xml containing only xml from the ScanRept column where the id is 4.
I don't want to do this interactively (by going to Studio Manager, finding the line, right clicking on the field and doing Save AS) - what will I do if I can't find a better way,
I have C #; if it is doable with sqlcmd this is my preference (because there are likely to be many changes that I cannot foresee right now).
source to share
There is probably a better way to do this ... but without testing, something like this might work. FROM#
SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, ScanRept FROM TableName", "connString");
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
string type = row["ID"].ToString();
string location = "C\\" + type + ".xml";
string xml = row["ScanRept"].ToString();
XmlTextWriter writer = new XmlTextWriter(location, null);
writer.WriteString(xml);
writer.Flush();
writer.Close();
}
source to share
Command line approach with bcp
:
bcp "SELECT ScanRept FROM <DBName>.dbo.<TableName> WHERE ID = 4" queryout 4.xml -c -S<ServerName> -U<UserName> -P<Password>
or for reliable connections
bcp "SELECT ScanRept FROM <DBName>.dbo.<TableName> WHERE ID = 4" queryout 4.xml -c -S<ServerName> -T
Command line approach with sqlcmd
:
sqlcmd -U<Username> -P<Password> -S<ServerName> -h -1 -Q "SET NOCOUNT ON SELECT ScanRept FROM <DBName>.dbo.<TableName> WHERE ID = 4;" -o 4.xml
or for reliable connections
sqlcmd -E -S<ServerName> -h -1 -Q "SET NOCOUNT ON SELECT ScanRept FROM <DBName>.dbo.<TableName> WHERE ID = 4;" -o 4.xml
source to share
Thank you both - good tips
Pano, it truncates after a certain length - is that 258 characters?
Toytown that works besides writer.WriteRaw () is what I need, or else it encodes the XML markup to make it valid data, for example turns into an lt ampersand with a semicolon
So far, I cant get anything but one long xml stream (no indentation, no line breaks). I'm not sure if there is any white space formatting in my data, but I know when I right click on a field in SSMS it brings up the MS XML editor showing everything is formatted pretty.
Okay, as the French say, "This damn XML is a pain in French Too-SHEE." Both of your comments are helpful, and I will vote on your answers as soon as I register this public identity thing.
Here's another approach that would allow him to write it pretty like a hot girl franch:
string IRIXno = args[0] ;
string connectionString ;
string query = "Select ID, Report FROM Reports WHERE id =" + IRIXno;
try
{
connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
}
catch
{
Console.WriteLine("YOur connection string isn't set or something.");
return;
}
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand sqlComm = new SqlCommand(query, conn);
sqlComm.CommandType = CommandType.Text;
SqlDataReader reader = sqlComm.ExecuteReader();
reader.Read(); // one and only row
XmlDocument doc = new XmlDocument();
XmlReader xmlReader = reader.GetSqlXml(1).CreateReader() ;//second column is our guy
doc.Load(xmlReader);
string outfile = "H:\\" + IRIXno + ".xml";
doc.Save(outfile) ;