Solved

LINQ to XML reading binary string from SQL stored procedure

Posted on 2008-10-08
27
426 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 63

Expert Comment

by:Fernando Soto
ID: 22686683
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
ID: 22686865
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 63

Expert Comment

by:Fernando Soto
ID: 22687067
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:psuscott
ID: 22722095
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
ID: 22723861
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 63

Expert Comment

by:Fernando Soto
ID: 22767206
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 63

Expert Comment

by:Fernando Soto
ID: 22767273
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
ID: 22767328
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 63

Expert Comment

by:Fernando Soto
ID: 22768696
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
ID: 22771681
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 63

Expert Comment

by:Fernando Soto
ID: 22772083
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
ID: 22772964
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 63

Expert Comment

by:Fernando Soto
ID: 22773287
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 22807795
Is this still an issue?
0
 

Author Comment

by:psuscott
ID: 22833572
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 63

Expert Comment

by:Fernando Soto
ID: 22833823
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
ID: 22849496
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 63

Expert Comment

by:Fernando Soto
ID: 22850872
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
ID: 22852324
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 63

Expert Comment

by:Fernando Soto
ID: 22853382
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 63

Expert Comment

by:Fernando Soto
ID: 22853420
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
ID: 22853454
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 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 22853593
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
ID: 22853728
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 63

Expert Comment

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

Author Closing Comment

by:psuscott
ID: 31504259
thank you very much!!
0
 
LVL 63

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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