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
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
sql.jpg
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>
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.
Can you post the the stored procedure and the actual code being used to create it XML.
ASKER
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> %>
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> %>
ASKER
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
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
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
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
ASKER
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
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>
ASKER
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
"Q2FyZSBJbnN0cnVjdGlvbnMKT GF3IExhYmV sClBhY2tlZ CBpbiBw... .......... 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
"Q2FyZSBJbnN0cnVjdGlvbnMKT
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.
ASKER
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
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 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?
ASKER
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.
ASKER
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
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
ASKER
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
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. ;=)
ASKER
thank you very much!!
Not a problem, glad I was able to help. ;=)
Does the code you posted work? If not, is it giving errors?
If the code is working is it giving the results wanted?
Fernando