Determining SQL Query for XML Formatting

Question

I'm building a tool for my own use that among other functions allows me to import and export tables from an SQL database into XML format. My company has a tool that does this already but only allows the import/export and not the other functions that need to be added to it. I have a copy of some of the XML files that has been output by our old tool and I'm looking to replicate the format that it outputs in. Below is that format.

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="ini_files">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="instance" type="xs:int" minOccurs="0" />
                <xs:element name="file" type="xs:string" minOccurs="0" />
                <xs:element name="section" type="xs:string" minOccurs="0" />
                <xs:element name="key" type="xs:string" minOccurs="0" />
                <xs:element name="value" type="xs:string" minOccurs="0" />
                <xs:element name="last_updated" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <ini_files>
    <instance>0</instance>
    <file>versions.ini</file>
    <section>Actual Versions</section>
    <key>28.000</key>
    <value>28.000</value>
    <last_updated>2015-08-25T17:44:10Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>User</section>
    <key>CD Key</key>
    <value>86BC011090861C115020</value>
    <last_updated>2016-02-17T03:32:20Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>Install</section>
    <key>Serial Number</key>
    <value>492679715G806LS|1570621586</value>
    <last_updated>2016-02-19T00:23:40Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>User</section>
    <key>Connect</key>
    <value>dsconnection</value>
    <last_updated>2016-02-17T03:32:20Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>dsconnection</section>
    <key>IP Address</key>
    <value>127.0.0.1,192.168.0.1,192.168.1.1</value>
    <last_updated>2016-02-26T00:26:25Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>dsconnection</section>
    <key>Port</key>
    <value>8080</value>
    <last_updated>2015-08-25T17:44:11Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>dsconnection</section>
    <key>Company Type</key>
    <value>1</value>
    <last_updated>2016-02-17T03:32:08Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>dsconnection</section>
    <key>Environment</key>
    <value>3Ljf10vSut+g5Pcsu4qwVnjwCN4SEoL8</value>
    <last_updated>2016-02-26T00:26:26Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>User</section>
    <key>Address</key>
    <value>TESTING--ADDRES--VALUE</value>
    <last_updated>2016-02-19T00:23:57Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>User</section>
    <key>Temporary Address</key>
    <value>78577739280119836672551</value>
    <last_updated>2016-02-17T03:32:09Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>versions.ini</file>
    <section>Profile Versions</section>
    <key>Last Modified</key>
    <value>2016-02-22T14:22:49</value>
    <last_updated>2016-02-22T21:22:49Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>dsconnection.ini</file>
    <section>dsconnection</section>
    <key>Last Connected</key>
    <value>2016-02-25T17:26:36</value>
    <last_updated>2016-02-26T00:26:36Z</last_updated>
  </ini_files>
  <ini_files>
    <instance>0</instance>
    <file>desktop.ini</file>
    <section>Online</section>
    <key>Last Sent Info</key>
    <value>2016-02-25T01:43:09.3739698Z</value>
    <last_updated>2016-02-25T01:43:09Z</last_updated>
  </ini_files>
</NewDataSet>

I'm pretty inexperienced with SQL but here is the query that I found to allow me to export to XML from our table, but the format is completely off.

osql -S (local)\instancename -U sa -P P@ssW0rd-d db_name -oC:\testing.xml -Q"SELECT * from ini_files for XML AUTO, BINARY BASE64"

If I don't export in BINARY BASE64 it throws this error.

FOR XML AUTO requires primary keys to create references for 'data'.
Select primary keys, or use BINARY BASE64 to obtain binary data in
encoded form if no primary keys exist.

I tried using XML RAW as well and it throws this error.

FOR XML EXPLICIT and RAW modes currently do not support addressing
binary data as URLs in column 'data'. Remove the column, or use the
BINARY BASE64 mode, or create the URL directly using the
'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.

I did a bit of searching but wasn't able to find anything concrete on replicating the output.

Can anyone identify what command is being used to export the XML listed above? Any help would be greatly appreciated.

As of SQL Server 2005, you should stop using isql and osql and use sqlcmd instead — marc_s
– marc_s, Commented Feb 26, 2016 at 19:59

Cistoran · Accepted Answer · 2016-02-26 20:20:08Z

Found my own answer. The format is part of .NET/C#. Here's the code I used to generate it.

        string sExportTableQuery = "SELECT* from ini_files";
        sSharedServer = "(local)\\instancename";
        sSharedDatabase = "db_name";
        sSharedUser = "sa";
        sSharedPassword = "P@ssW0rd";
        string sConnectionString = "Server=" + sSharedServer + ";Database=" + sSharedDatabase + ";User Id=" + sSharedUser + ";Password=" + sSharedPassword + ";";
        using (SqlConnection sqlConnection = new SqlConnection(sConnectionString))
        {
            SqlCommand sqlCommand = new SqlCommand(sExportTableQuery, sqlConnection);
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ds.Tables[0].WriteXml(@"C:\text.xml", XmlWriteMode.WriteSchema);
        }

Collectives™ on Stack Overflow

Determining SQL Query for XML Formatting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related