Merge XML after existing XML to create QBXML FILE

corey gashlin
corey gashlin used Ask the Experts™
on
I have my code in C# to create the outershell of the file I need(QBXML).
I then have it call my function to query SQL and get the info that need to go inside of it under the <QBXMLMsgRq> element

The first part creates this -
<?xml version="1.0"?>
<?qbxml version="13.0"?>
<QBXML>
  <QBXMLMsgsRq onError="stopOnError" />
</QBXML>

Open in new window


Then it called my "BuildCustomerAddRq"

Which is this
 void BuildCustomerAddRq(XmlDocument doc, XmlElement parent)
    {

        try
        {
            string ConString = "Data Source=192.168.0.40;Initial Catalog=OCITIME;User ID=ocisql;Password=Emagdne1212";
            string CmdString = "select employee_name as Name ,'OCI Associates' as [CompanyName],'Mr' as Salutation , LEFT(employee_name, CHARINDEX(' ', employee_name + ' ') - 1) as FirstName,  REVERSE(LEFT(REVERSE(employee_name),    CHARINDEX(' ', REVERSE(employee_name)) - 1)) AS LastName   from EMPLOYEELIST for xml raw('CustomerAdd') , ROOT('CustomerAddRq'), ELEMENTS";

            SqlConnection con;
            SqlCommand cmd;
            XmlReader reader;
            XmlDocument xmlDoc;

            using (con = new SqlConnection(ConString))
            {
                cmd = new SqlCommand(CmdString, con);
                con.Open();
                reader = cmd.ExecuteXmlReader();
          

                while (reader.Read())
                {
             
                    doc.Load(reader);
                   
                }
                doc.AppendChild(doc.CreateXmlDeclaration("1.0", null, null));
                doc.AppendChild(doc.CreateProcessingInstruction("qbxml", "version=\"13.0\""));
                doc.Save("C:\\Employees.xml");
            }
        }
        catch (Exception)
        {
            throw;
        }


    }

Open in new window


Here is the build request that runs first -

//This is the shit to create requests and submit to QUICKBOOKS
    public ArrayList buildRequest()
    {
        XmlDocument requestXmlDoc = new XmlDocument();
        //Add the prolog processing instructions
        requestXmlDoc.AppendChild(requestXmlDoc.CreateXmlDeclaration("1.0", null, null));
        requestXmlDoc.AppendChild(requestXmlDoc.CreateProcessingInstruction("qbxml", "version=\"13.0\""));

        //Create the outer request envelope tag
        XmlElement outer = requestXmlDoc.CreateElement("QBXML");
        requestXmlDoc.AppendChild(outer);

        //Create the inner request envelope & any needed attributes
        XmlElement inner = requestXmlDoc.CreateElement("QBXMLMsgsRq");
        outer.AppendChild(inner);
        inner.SetAttribute("onError", "stopOnError");
        BuildCustomerAddRq(requestXmlDoc, inner);
       

        string strRequestXML = requestXmlDoc.OuterXml;
        req.Add(strRequestXML);
        requestXmlDoc.Save("C:\\testing123.xml");




        return req;
    }

Open in new window


I need it to spit something out like this -

<?xml version="1.0"?>
<?qbxml version="13.0"?>
<QBXML>
  <QBXMLMsgsRq onError="stopOnError">
    <CustomerAddRq requestID="1">
      <CustomerAdd>
        <Name>Fake Name</Name>
        <CompanyName>Fake Company</CompanyName>
        <Salutation>Mr.</Salutation>
        <FirstName>Fake</FirstName>
        <MiddleName>P</MiddleName>
        <LastName>Name</LastName>
        <JobTitle>Systems Engineer</JobTitle>
        <BillAddress>
          <Addr1>1234 Fake Address</Addr1>
        </BillAddress>
        <ShipToAddress>
          <Name>Fake Name</Name>
        </ShipToAddress>
        <Phone>123-126-123</Phone>
        <AltPhone>123-123-123</AltPhone>
        <Email>gashlin@gmail.com</Email>
      </CustomerAdd>
    </CustomerAddRq>
  </QBXMLMsgsRq>
</QBXML>

Open in new window



I know it has to be something simple but am drawing a blank.

Currently it is only giving me this

<CustomerAddRq>
  <CustomerAdd>
    <Name>Fake Name</Name>
    <CompanyName>Fake Company</CompanyName>
    <Salutation>Mr</Salutation>
    <FirstName>Fake</FirstName>
    <LastName>Name</LastName>
  </CustomerAdd>
</CustomerAddRq>

Open in new window


Looking to have them combined properly
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Update -

i was able to get it kind of right using this code -

public ArrayList buildRequest()
    {
        // TableToXml();
        XmlDocument requestXmlDoc = new XmlDocument();
      

        requestXmlDoc.AppendChild(requestXmlDoc.CreateXmlDeclaration("1.0", null, null));
        requestXmlDoc.AppendChild(requestXmlDoc.CreateProcessingInstruction("qbxml", "version=\"13.0\""));
        XmlElement qbXML = requestXmlDoc.CreateElement("QBXML");
        requestXmlDoc.AppendChild(qbXML);
        XmlElement qbXMLMsgsRq = requestXmlDoc.CreateElement("QBXMLMsgsRq");
        qbXML.AppendChild(qbXMLMsgsRq);
        qbXMLMsgsRq.SetAttribute("onError", "stopOnError");

        //New XML DOCUMENT ?
        string ConString = "Data Source=192.168.0.40;Initial Catalog=OCITIME;User ID=ocisql;Password=Emagdne1212";
        string CmdString = "select employee_name as Name ,'Mr' as Salutation , LEFT(employee_name, CHARINDEX(' ', employee_name + ' ') - 1) as FirstName,  REVERSE(LEFT(REVERSE(employee_name),    CHARINDEX(' ', REVERSE(employee_name)) - 1)) AS LastName   from EMPLOYEELIST where EMPLOYEE_CODE='1141' for xml raw('CustomerAdd') , ROOT('CustomerAddRq'), ELEMENTS";

        SqlConnection con;
        SqlCommand cmd;
        XmlReader reader;
        XmlDocument xmlDoc;

        using (con = new SqlConnection(ConString))
        {
            cmd = new SqlCommand(CmdString, con);
            con.Open();
            reader = cmd.ExecuteXmlReader();
            xmlDoc = new XmlDocument();
            while (reader.Read())
            {
                xmlDoc.Load(reader);
            }
            StringWriter sq = new StringWriter();

            XmlTextWriter tx = new XmlTextWriter(sq);
            xmlDoc.WriteTo(tx);
            sq.ToString();
            XmlDocumentFragment xmlDocFrag = requestXmlDoc.CreateDocumentFragment();
            xmlDocFrag.InnerXml = sq.ToString();
            XmlElement rootElement = requestXmlDoc.DocumentElement;
            rootElement.AppendChild(xmlDocFrag);
     

        }

        string strRequestXML = requestXmlDoc.OuterXml;
        req.Add(strRequestXML);
        requestXmlDoc.Save(Server.MapPath("~text.xml"));
        requestXmlDoc.Save("C:\\FUCKTHIS.xml");




        return req;
    }

Open in new window


however ...

It gives me this

<?xml version="1.0"?>
<?qbxml version="13.0"?>
<QBXML>
  <QBXMLMsgsRq onError="stopOnError" />
  <CustomerAddRq>
    <CustomerAdd>
      <Name>Corey Gashlin</Name>
      <Salutation>Mr</Salutation>
      <FirstName>Corey</FirstName>
      <LastName>Gashlin</LastName>
    </CustomerAdd>
  </CustomerAddRq>
</QBXML>

Open in new window


I need this -
<?xml version="1.0"?>
<?qbxml version="13.0"?>
<QBXML>
  <QBXMLMsgsRq onError="stopOnError">
  <CustomerAddRq>
    <CustomerAdd>
      <Name>Corey Gashlin</Name>
      <Salutation>Mr</Salutation>
      <FirstName>Corey</FirstName>
      <LastName>Gashlin</LastName>
    </CustomerAdd>
  </CustomerAddRq>
 </QBXMLMsgsRq>
</QBXML>

Open in new window


See the QBXMLMsgRq is end itself early. needs to be in the correct location.
Fixed with this
            XmlDocumentFragment xmlDocFrag = requestXmlDoc.CreateDocumentFragment();
            xmlDocFrag.InnerXml = sq.ToString();
            XmlElement rootElement = (XmlElement)requestXmlDoc.SelectSingleNode("/QBXML//QBXMLMsgsRq");
            rootElement.AppendChild(xmlDocFrag);

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial