Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

LINQ to XML reading binary string from SQL stored procedure

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
psuscott
Asked:
psuscott
  • 15
  • 12
1 Solution
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
psuscottAuthor Commented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
Fernando SotoRetiredCommented:
Is this still an issue?
0
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
it highlights text.t_text and says "value of type 'system.data.linq.binary' cannot be converted to '1-dimensional array of byte'
0
 
Fernando SotoRetiredCommented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
psuscottAuthor Commented:
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
 
Fernando SotoRetiredCommented:
Not a problem, talk to you later.  ;=)
0
 
psuscottAuthor Commented:
thank you very much!!
0
 
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now