How do I fix SQL so XML result displays correctly in excel?

Question

0.00/5 (No votes)

See more:

In a function I used KEDIT for windows to write a query in vb.net which returns a xml document and then that xml is displayed as Excel spreadsheet. The problem I have is that two column data is being split and then entered into the next column. How do I fix this?

Here is my function

VB

Private Shared Function GetSql(ByVal adtmStartDateTime As Date, ByVal adtmEndDateTime As Date) As String
        Dim strSQL As String

        strSQL = "--DROP TABLE #TEMP_Request" + vbCrLf +
    "" + vbCrLf +
    ";WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS soap," + vbCrLf +
    "'http://niem.gov/niem/niem-core/2.0' AS nc," + vbCrLf +
    "'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:CommonTypes-4.0' AS ecf," + vbCrLf +
    "Default 'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:DocumentQueryMessage-4.0')" + vbCrLf +
    "SELECT MessageID, ProcessDateTime AS 'RequestDateTime'," + vbCrLf +
    "Convert(Xml, InputMessage.Body).value('(//*[local-name()=""DocumentQueryMessage""]/nc:CaseTrackingID)[1]','varchar(250)') AS 'CaseNumber'" + vbCrLf +
    "INTO #TEMP_Request" + vbCrLf +
    "FROM InputMessage" + vbCrLf +
    "WHERE" + vbCrLf +
    "(QueueName = 'ECF_COURTRECORD_QUERY_02')" + vbCrLf +
    "And (ProcessDateTime >= CONVERT(DATETIME, '" + adtmStartDateTime.ToString + "', 102))" + vbCrLf +
     "AND (ProcessDateTime < CONVERT(DATETIME, '" + adtmEndDateTime.ToString + "', 102))" + vbCrLf +
     "And" + vbCrLf +
     "CONVERT(XML, InputMessage.Body).exist('(//*[local-name()=""DocumentQueryMessage""])') = 1" + vbCrLf +
    ";WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS soap," + vbCrLf +
    "'http://niem.gov/niem/niem-core/2.0' AS nc," + vbCrLf +
    "'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:CommonTypes-4.0' AS ecf," + vbCrLf +
    "'http://niem.gov/niem/domains/jxdm/4.0' AS d," + vbCrLf +
    "Default 'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:DocumentResponseMessage-4.0')" + vbCrLf +
    "SELECT " + vbCrLf +
    "(SELECT OM.MessageID, TR.RequestDateTime, TR.CaseNumber," + vbCrLf +
    "Convert(Xml, OM.Body).value('(//DocumentResponseMessage/ecf:Document/nc:DocumentDescriptionText)[1]','varchar(250)') AS 'DocumentName'," + vbCrLf +
    "Convert(Xml, OM.Body).value('(//DocumentResponseMessage/ecf:Document/ecf:DocumentMetadata/d:RegisterActionDescriptionText)[1]','varchar(250)') AS 'CaseEventDescription'," + vbCrLf +
    "Convert(Xml, OM.Body).value('(soap:Envelope/soap:Body/DocumentResponseMessage/ecf:Document/nc:DocumentFiledDate/nc:DateTime)[1]','datetime') AS 'DocumentFiledDate'" + vbCrLf +
    "FROM #TEMP_Request AS TR" + vbCrLf +
    "INNER JOIN  OutputMessage AS OM ON (TR.MessageID = OM.MessageID)" + vbCrLf +
    "WHERE" + vbCrLf +
       "Convert(Xml, OM.Body).value('(soap:Envelope/soap:Body/DocumentResponseMessage/ecf:Document/nc:DocumentIdentification/nc:IdentificationID)[1]','varchar(250)') IS NOT NULL " + vbCrLf +
        "For Xml  PATH('DocumentGetAuditReport'),  Type)  " + vbCrLf +
           "For Xml PATH('Results') "

        Return strSQL
    End Function

Here is result in xml, the part that has a problem in Excel.

XML

<DocumentGetAuditReport xmlns="urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:DocumentResponseMessage-4.0" xmlns:d="http://niem.gov/niem/domains/jxdm/4.0" xmlns:ecf="urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:CommonTypes-4.0" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
    <MessageID>422A</MessageID>
    <RequestDateTime>2018-07-20T15:06:39.087</RequestDateTime>
    <OrganizationORI>TP0070</OrganizationORI>
    <OrganizationName>St Martin County Attorney</OrganizationName>
    <CaseNumber>00-CV-00-001</CaseNumber>
    <DocumentName>Findings, Conclusions & Orderfor Disbursal of Funds to petitioner (Comdemnation for Roadway Purposes</DocumentName>
    <CaseEventDescription>Findings of Fact, Conclusions of Law and Order</CaseEventDescription>
    <DocumentFiledDate>2017-02-28T15:46:39</DocumentFiledDate>
  </DocumentGetAuditReport>

Here is the Excel result
"DocumentGet Integration Query Service Documents Requested and Provided July 2018"
Request DateTime Case Number Document Name Case Event Description Document Filed Date
2018-07-20T14:32:31.247 00-CV-00-001 Findings Conclusions & Orderfor Disbursal of Funds to petitioner (Comdemnation for Roadway Purposes Findings of Fact Conclusions of Law and Order 2/28/2017

Correct output should have Document Name column with the following data
Findings, Conclusions & Orderfor Disbursal of Funds to petitioner (Comdemnation for Roadway Purposes

Currently this column on have Findings

Case Event Description column should have
Findings of Fact, Conclusions of Law and Order

Currently this column have Conclusions & Orderfor Disbursal of Funds to petitioner (Comdemnation for Roadway Purposes which is wrong

Document Filed Date column has Findings of Fact. This is wrong. It should have the 2/28/2017 date instead

How do I fix my KEDIT or what do I need to do so the data in each column is not cut off?

Thanks in advance.

What I have tried:

VB

Private Shared Function GetSql(ByVal adtmStartDateTime As Date, ByVal adtmEndDateTime As Date) As String
        Dim strSQL As String

        strSQL = "--DROP TABLE #TEMP_Request" + vbCrLf +
    "" + vbCrLf +
    ";WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS soap," + vbCrLf +
    "'http://niem.gov/niem/niem-core/2.0' AS nc," + vbCrLf +
    "'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:CommonTypes-4.0' AS ecf," + vbCrLf +
    "Default 'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:DocumentQueryMessage-4.0')" + vbCrLf +
    "SELECT MessageID, ProcessDateTime AS 'RequestDateTime'," + vbCrLf +
    "Convert(Xml, InputMessage.Body).value('(//*[local-name()=""DocumentQueryMessage""]/nc:CaseTrackingID)[1]','varchar(250)') AS 'CaseNumber'" + vbCrLf +
    "INTO #TEMP_Request" + vbCrLf +
    "FROM InputMessage" + vbCrLf +
    "WHERE" + vbCrLf +
    "(QueueName = 'ECF_COURTRECORD_QUERY_02')" + vbCrLf +
    "And (ProcessDateTime >= CONVERT(DATETIME, '" + adtmStartDateTime.ToString + "', 102))" + vbCrLf +
     "AND (ProcessDateTime < CONVERT(DATETIME, '" + adtmEndDateTime.ToString + "', 102))" + vbCrLf +
     "And" + vbCrLf +
     "CONVERT(XML, InputMessage.Body).exist('(//*[local-name()=""DocumentQueryMessage""])') = 1" + vbCrLf +
    ";WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS soap," + vbCrLf +
    "'http://niem.gov/niem/niem-core/2.0' AS nc," + vbCrLf +
    "'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:CommonTypes-4.0' AS ecf," + vbCrLf +
    "'http://niem.gov/niem/domains/jxdm/4.0' AS d," + vbCrLf +
    "Default 'urn:oasis:names:tc:legalxml-courtfiling:schema:xsd:DocumentResponseMessage-4.0')" + vbCrLf +
    "SELECT " + vbCrLf +
    "(SELECT OM.MessageID, TR.RequestDateTime, TR.CaseNumber," + vbCrLf +
    "Convert(Xml, OM.Body).value('(//DocumentResponseMessage/ecf:Document/nc:DocumentDescriptionText)[1]','varchar(250)') AS 'DocumentName'," + vbCrLf +
    "Convert(Xml, OM.Body).value('(//DocumentResponseMessage/ecf:Document/ecf:DocumentMetadata/d:RegisterActionDescriptionText)[1]','varchar(250)') AS 'CaseEventDescription'," + vbCrLf +
    "Convert(Xml, OM.Body).value('(soap:Envelope/soap:Body/DocumentResponseMessage/ecf:Document/nc:DocumentFiledDate/nc:DateTime)[1]','datetime') AS 'DocumentFiledDate'" + vbCrLf +
    "FROM #TEMP_Request AS TR" + vbCrLf +
    "INNER JOIN  OutputMessage AS OM ON (TR.MessageID = OM.MessageID)" + vbCrLf +
    "WHERE" + vbCrLf +
       "Convert(Xml, OM.Body).value('(soap:Envelope/soap:Body/DocumentResponseMessage/ecf:Document/nc:DocumentIdentification/nc:IdentificationID)[1]','varchar(250)') IS NOT NULL " + vbCrLf +
        "For Xml  PATH('DocumentGetAuditReport'),  Type)  " + vbCrLf +
           "For Xml PATH('Results') "

        Return strSQL
    End Function

Posted 23-Jul-18 6:18am

Member 11403304

Add a Solution

Comments

MadMyche 23-Jul-18 14:14pm

Just because SQL can do XML, doesn't mean you should do it that way. My general rule of thought is to just get the data from SQL and then use the application to create the desired presentation.
And all your code does is create a string containing an SQL command. Nothing to actually do anything with that command

Alek Massey 25-Jul-18 12:47pm

Excel is interpreting commas in your data as a delimeter between separate values.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)