Solved

LINQ to XML reading binary string from SQL stored procedure

Posted on 2008-10-08
27
424 Views
Last Modified: 2013-11-26
In an attempt to write a program in visual studio 2008 that will build an xml file with po information I have reached a point where I am stuck with trying to call a stored procedure with four lines of code into the xml file. It is reading the stored procedure because it is bringing back Build_XML.gettextResult four times. If there is anyone more experienced at writing this type of code please advise how to correct this code. Thank you for your time. Below is hopefully the information  you will need to understand what I am looking at. I have included the stored procedure as gettext in the .dmbl tab in visual studio 2008 as well.

Stored procedure(gettext)

CREATE PROCEDURE gettext AS
      select cast(t_text as char) as t_test  from ttttxt010500
      where t_ctxt = 125421
GO

Module Module1
 

 Sub Main()

  Dim db As New BAANDataContext

  Dim orders = <orders>

        <%= From text In db.gettext _

            Select <text>

                      <text><%= text %></text>

                   </text> %>

       </orders>
 

  orders.Save("C:\Users\scsowers\Desktop\orders.xml")

  Process.Start("C:\Users\scsowers\Desktop\orders.xml")
 

 End Sub
 

End Module

Open in new window

sql.jpg
0
Comment
Question by:psuscott
  • 15
  • 12
27 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Hi psuscott;

Does the code you posted work? If not, is it giving errors?
If the code is working is it giving the results wanted?

Fernando
0
 

Author Comment

by:psuscott
Comment Utility
the code does produce a result i will post it here...however the area i mentioned <text><%= text %></text> only returns Build_XML.gettextResult i need it to return what is depicted in the screen shot of sql server


<shipment>

	<shipment_header>

		<transport_type>7-52356-78212-2</transport_type> 

		<bl_number /> 

		<hbl_number /> 

		<master_bl_reference_number /> 

		<creation_date /> 

		<destination_station_station_code /> 

		<origin_station_station_code /> 

		<rate_amount /> 

		<purchase_orders>

			<purchase_order>

				<po_number>10</po_number> 

			</purchase_order>

		</purchase_orders>

	</shipment_header>

<trading_partners>

	<participant>

		<business_name_reference_number>

		<address /> 

		<state_province /> 

		<country /> 

		</business_name_reference_number>

	</participant>

</trading_partners>

<order>

	<number>104467</number> 

	<quantity>4260</quantity> 

</order>

<dates>

	<cancel>2008-08-23T00:00:00</cancel> 

	<ship>2008-08-11T00:00:00</ship> 

</dates>

<text>

	<text>Build_XML.gettextResult</text> 

</text>

<text>

	<text>Build_XML.gettextResult</text> 

</text>

<text>

	<text>Build_XML.gettextResult</text> 

</text>

<text>

	<text>Build_XML.gettextResult</text> 

</text>

</shipment>

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Is your last post the output, the XML file you are building?
Can you post the the stored procedure and the actual code being used to create it XML.
0
 

Author Comment

by:psuscott
Comment Utility
in the first post i included the stored procedure it is the:
CREATE PROCEDURE gettext AS
      select cast(t_text as char) as t_test  from ttttxt010500
      where t_ctxt = 125421
GO

the code i used is the part in the first post where the code snippet is, mainly the part:
        <%= From text In db.gettext _
            Select <text>
                      <text><%= text %></text>
                   </text> %>

0
 

Author Comment

by:psuscott
Comment Utility
perhaps if it is easier someone could explain how i can open a csv file that has the information in it and populate from that into xml?
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Hi psuscott;

I noticed that this is the some what the same question as the other. Just updating this post for any one that finds this question in search of an answer.

The code snippet below is a Console application that reads the csv file into memory parses the fields and builds the XML document and writes it out to a file. The csv file you posted has spaces in the header and cannot be used as is to create the XML document. The spaces need to be removed. I have attached the csv file I used to test the code. When the program is run it will produce the following file also attached. The file names need to be renamed as outlined in the file Description below.

Fernando
Imports System.IO

Imports System.Xml.Linq

Imports System.Xml

 

Module Module1

 

    Sub Main()

        ' Create an XML document with an XML declaration and a root element

        Dim doc As New XDocument(New XDeclaration("1.0", "uth-8", "yes"), _

                         New XElement("Root"))

        ' Get a refernce to the Root node to add additional element nodes

        Dim root As XElement = doc.Root

 

        ' Create and open a text field parser, the parser opens the file

        ' which is in the same directory as the executable. In the IDE invironment

        ' this directory is in \bin\Debug below the source files of the program.

        Using csvParser As New FileIO.TextFieldParser("sheet2.csv")

            ' Set up the field to read the fields correctly

            csvParser.Delimiters = New String() {","}

            csvParser.HasFieldsEnclosedInQuotes = True

            csvParser.TextFieldType = FileIO.FieldType.Delimited

            csvParser.TrimWhiteSpace = True

            ' In the example file given the first line of code has the field headers

            ' I used that in this code snippet to name the tags of the elements. I read

            ' all the headers into an array of strings to fill in the tags of the elements

            ' The headers in the file have spaces in them. You need to remove the spaces

            ' because they are not allowed in tag names.

            Dim header() As String = csvParser.ReadFields()

            ' This string array does the same as the header array but for the data of the elements

            Dim fields() As String

            While Not csvParser.EndOfData()

                ' get all the fields and read it into the string array

                fields = csvParser.ReadFields()

                ' A nice thing about VB .Net 2008 is that it allows what is called functional

                ' construction as seen in the next couple of lines. Each tag of the elements

                ' fills in what is called a hole like this, <%= header(0) %>, the header(0) is

                ' the value of the variable header at element 0.

                Dim xmlFieldData As XElement = <CompanyInfo>

                                                   <<%= header(0) %>><%= fields(0) %></>

                                                   <<%= header(1) %>><%= fields(1) %></>

                                                   <<%= header(2) %>><%= fields(2) %></>

                                                   <<%= header(3) %>><%= fields(3) %></>

                                                   <<%= header(4) %>><%= fields(4) %></>

                                                   <<%= header(5) %>><%= fields(5) %></>

                                                   <<%= header(6) %>><%= fields(6) %></>

                                                   <<%= header(7) %>><%= fields(7) %></>

                                                   <<%= header(8) %>><%= fields(8) %></>

                                                   <<%= header(9) %>><%= fields(9) %></>

                                                   <<%= header(10) %>><%= fields(10) %></>

                                                   <<%= header(11) %>><%= fields(11) %></>

                                                   <<%= header(12) %>><%= fields(12) %></>

                                                   <<%= header(13) %>><%= fields(13) %></>

                                                   <<%= header(14) %>><%= fields(14) %></>

                                                   <<%= header(15) %>><%= fields(15) %></>

                                                   <<%= header(16) %>><%= fields(16) %></>

                                                   <<%= header(17) %>><%= fields(17) %></>

                                                   <<%= header(18) %>><%= fields(18) %></>

                                                   <<%= header(19) %>><%= fields(19) %></>

                                                   <<%= header(20) %>><%= fields(20) %></>

                                                   <<%= header(21) %>><%= fields(21) %></>

                                               </CompanyInfo>

                ' Add the above elements to the Root node. 

                root.Add(xmlFieldData)

                ' Go back to the top and get the next row of data and do the same.

            End While

        End Using

 

        ' Write the XML data to a file, this is writing the info to the file called

        ' sheet2.xml in UFT-8 format.

        Dim xwriter As New XmlTextWriter("sheet2.xml", System.Text.Encoding.UTF8)

        ' The following three lines of code do a format of the XML to look nice

        ' otherwise the data is in one long line.

        xwriter.Formatting = Formatting.Indented

        xwriter.Indentation = 4

        xwriter.IndentChar = " "c

        ' Write the document to the file

        doc.WriteTo(xwriter)

        ' Close the document

        xwriter.Close()

 

    End Sub

 

End Module

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Hi psuscott;

The above post should have answered this question as well. You did not need to open a new question you could have asked in this thread.

Please close this question if done.

Thank you;
Fernando
0
 

Author Comment

by:psuscott
Comment Utility
it would be an acceptable alternative however i was most interested in displaying a result from a stored procedure through linq into an xml document here. i still hope to learn how that is possible at some point although if it goes too long without an answer i suppose i could just close this.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Hi psuscott;

Try changing the query as shown below. The query is an enumeration and the data field needs to be pulled out directly as shown below.

Fernando
Dim orders = <orders>

             <%= From text In db.gettext _

                 Select <text>

                            <text><%= text.t_test %></text>

                        </text> %>

             </orders>

Open in new window

0
 

Author Comment

by:psuscott
Comment Utility
well that is a start...instead of gettext result it displays the string but in a long encrypted sort of way. i will attach the file
"Q2FyZSBJbnN0cnVjdGlvbnMKTGF3IExhYmVsClBhY2tlZCBpbiBw.............etc"

it was about 2 million lines long because it was picking up all orders so i deleted all but the first one so you can see. i also renamed the extension to .txt so i could upload it here.
ordersxml.txt
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Can you post the code that you ran to get the results and the structure of the data source so that I can get a better understanding of what is happening. Or if the data source is from the other question let me know that then.
0
 

Author Comment

by:psuscott
Comment Utility
i trimmed it down to just do the text part and i changed it to do text.t_text like you stated above. the output is the xml file i attached in the last post it just outputs a long string which appears encrypted.
Module Module1

    Sub Main()

        Dim dbo As New BAANDataContext

        Dim orders = <shipment>
 

                         <%= From text In dbo.ttttxt010500s _

                             Select <text>

                                        <text><%= text.t_text %></text>

                                    </text> %>

                     </shipment>
 

        orders.Save("C:\Users\scsowers\Desktop\orders.xml")

        Process.Start("C:\Users\scsowers\Desktop\orders.xml")
 

    End Sub
 

End Module

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Seeming that the code is basically the same as I had ask you to change it to and the table that it reference has the structure as shown in the image below, the t_text field is only 240 bits. Now if the bits only represents character data, that field can only hold 31 characters. So a question:

Is the field t_text in the DB 240 bits as shown in the image below?
Is the field encrypted before being written to the DB?
If it is then can you post the code that writes to this table in the DB from your code.

SP32-111.jpg
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Is this still an issue?
0
 

Author Comment

by:psuscott
Comment Utility
i apologize for the delay, i stopped getting emails that there were comments posted for some reason? to answer the previous post: 1) yes the t_text is 240bit, 2) i am not sure on this, how would i find that information out?, 3) i will gather that information within the hour and post a code snippet.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
To my question, "Is the field encrypted before being written to the DB?", about the only way is to look at the code that writes the database and see if the string of data is first encrypted before being stored.
0
 

Author Comment

by:psuscott
Comment Utility
the data is written with a erp package called baan, it is not open source code so i cannot access that session. i am assuming the data is encrypted because in this code:
CREATE PROCEDURE gettext AS
      select cast(t_text as char) as t_test  from ttttxt010500
      where t_ctxt = 125421
GO
i had to use cast to get the data to display as entered. without that it is a long string of letters and numbers
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Well to me that states that the information is not encrypted. Try the snippet of code below to see if that works.

Module Module1

    Sub Main()

        Dim dbo As New BAANDataContext

        Dim orders = <shipment>

 

                         <%= From text In dbo.ttttxt010500s _

                             Select <text>

                                        <text><%= System.Text.Encoding.Default.GetString(text.t_text) %></text>

                                    </text> %>

                     </shipment>

 

        orders.Save("C:\Users\scsowers\Desktop\orders.xml")

        Process.Start("C:\Users\scsowers\Desktop\orders.xml")

 

    End Sub

 

End Module

Open in new window

0
 

Author Comment

by:psuscott
Comment Utility
it highlights text.t_text and says "value of type 'system.data.linq.binary' cannot be converted to '1-dimensional array of byte'
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Then this should work.

Module Module1

    Sub Main()

        Dim dbo As New BAANDataContext

        Dim orders = <shipment>

 

                         <%= From text In dbo.ttttxt010500s _

                             Select <text>

                                        <text><%= System.Text.Encoding.UTF8.GetString(text.t_text.ToArray()) %></text>

                                    </text> %>

                     </shipment>

 

        orders.Save("C:\Users\scsowers\Desktop\orders.xml")

        Process.Start("C:\Users\scsowers\Desktop\orders.xml")

 

    End Sub

 

End Module

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Well I take that back. It should work if the data stored in the Database is in UTF-8 format. I was able to reproduce the problem on my system but I wrote the data to the database in UTF-8 format. If it does not work on your system we will need to change the format to we get it right.
0
 

Author Comment

by:psuscott
Comment Utility
that did not give an error before debugging however once the program ran the step orders.save returned an error that says ' ', hexadecimal value 0x12, is an invalid character. do you know what this means?
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
Replace this line in the code:
<text><%= System.Text.Encoding.UTF8.GetString(text.t_text.ToArray()) %></text>

With one of these until you get favorable results. Hopefully one of them will work.
 
<text><%= System.Text.Encoding.ASCII.GetString(text.t_text.ToArray()) %></text>
<text><%= System.Text.Encoding.Default.GetString(text.t_text.ToArray()) %></text>
<text><%= System.Text.Encoding.Unicode.GetString(text.t_text.ToArray()) %></text>
<text><%= System.Text.Encoding.UTF7.GetString(text.t_text.ToArray()) %></text>

The error code you got is meaningless unless we are using the correct encoding.


0
 

Author Comment

by:psuscott
Comment Utility
the first one actually did work it just took a really really long time to open the xml document, the text is visible there however it breaks apart at random areas, i will try the others this weekend and post again first thing monday. thanks so much for your help you are a very knowledgable person on this subject.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem, talk to you later.  ;=)
0
 

Author Closing Comment

by:psuscott
Comment Utility
thank you very much!!
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem, glad I was able to help.  ;=)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now