Avatar of corey gashlin
corey gashlin

asked on 

Merge XML after existing XML to create QBXML FILE

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
XMLC#QuickBooksGolangSQL

Avatar of undefined
Last Comment
corey gashlin
Avatar of corey gashlin
corey gashlin

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of corey gashlin
corey gashlin

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
C#
C#

C# is an object-oriented programming language created in conjunction with Microsoft’s .NET framework. Compilation is usually done into the Microsoft Intermediate Language (MSIL), which is then JIT-compiled to native code (and cached) during execution in the Common Language Runtime (CLR).

98K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo