Link to home
Start Free TrialLog in
Avatar of psuscott
psuscott

asked on

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
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

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
Avatar of psuscott
psuscott

ASKER

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

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.
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> %>

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?
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

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
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.
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

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
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.
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

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
Is this still an issue?
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.
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.
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
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

it highlights text.t_text and says "value of type 'system.data.linq.binary' cannot be converted to '1-dimensional array of byte'
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

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.
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?
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Not a problem, talk to you later.  ;=)
thank you very much!!
Not a problem, glad I was able to help.  ;=)