SQL Server 2005 - get an XML field into a file; some xml fields into their own files

Question

I got a row structure looks like ID bigint, ScanRept XML

I want to wind up with a file named 4.xml containing just the xml from the ScanRept column where the ID number is 4.

I don't want to do it interactively (by going into Manager Studio, finding the row, right-clicking the field and doing Save AS) - that's what I'll do if I can't figure out a better way.

I do have C# available; if this is doable with sqlcmd, that's my preference (cause there will probably be a lot of variations I can't anticipate right now).

Answer 1

There is probably a better way to do this...but without testing something like this might work.C#

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();
}

Answer 2

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 trusted 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 trusted connections

sqlcmd -E -S<ServerName> -h -1 -Q "SET NOCOUNT ON SELECT ScanRept FROM <DBName>.dbo.<TableName> WHERE ID = 4;" -o 4.xml

Answer 3

Thanks both of you - good hints

Panos, it truncates after a certain lenght - which is 258 characters?

Toytown, that works except writer.WriteRaw() is what I need, or else it encodes the XML markup to make it valid data, e.g., < gets turned into ampersand lt semicolon

So far I can't get anything but one long stream of xml (no indenting, no linebreaks). I'm not sure whether there's any formatting whitespace in my data, but I know when I right click the field in SSMS, it brings up MS XML Editor showing it all formatted pretty.

Well, as the French say, "That damn XML is a pain in ma French Too-SHEE". Both of your comments help and I'll vote for your answers once I register on this dang open id thing.

Answer 4

Here's another approach that let it write it out pretty, the way hot franch girlz like:

        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) ;