Help with SQL Mapping

First time trying to import a pretty simple XML file into a SQL
table. I've been reading annotated XSD file docs for the past 3 hours
and it's just not clear to me. Here's my XML
<?xml version="1.0" standalone="yes"?>
<InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>12345</documentnumber>
<invoicebalance>99.99</invoicebalance>
<invoicenumber>INV123</invoicenumber>
<invoicetrantype>FS</invoicetrantype>
</InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>444</documentnumber>
<invoicebalance>88.88</invoicebalance>
<invoicenumber>INV345</invoicenumber>
<invoicetrantype>AB</invoicetrantype>
</InvoiceBalanceData>
</InvoiceBalanceData>
Can someone help me with my annotated XSD file? tia.
On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> First time trying to import a pretty simple XML file into a SQL
> table. I've been reading annotated XSD file docs for the past 3 hours
> and it's just not clear to me. Here's my XML
> <?xml version="1.0" standalone="yes"?>
> <InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>12345</documentnumber>
> <invoicebalance>99.99</invoicebalance>
> <invoicenumber>INV123</invoicenumber>
> <invoicetrantype>FS</invoicetrantype>
> </InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>444</documentnumber>
> <invoicebalance>88.88</invoicebalance>
> <invoicenumber>INV345</invoicenumber>
> <invoicetrantype>AB</invoicetrantype>
> </InvoiceBalanceData>
> </InvoiceBalanceData>
> Can someone help me with my annotated XSD file? tia.
I should add that after I attempt to import the XML, the script
finishes successfully (this is from a DTS package), but no rows are
imported into the table.
|||I would process it as attribute centric
declare @.xml xml
SET @.xml =
'<root>
<InvoiceBalanceData documentnumber="12345" invoicebalance="99.99"
invoicenumber="INV123" invoicetrantype="FS" />
<InvoiceBalanceData documentnumber="444" invoicebalance="88.88"
invoicenumber="INV345" invoicetrantype="AB" />
</root>'
select @.xml
SELECT
[inv].[ref].value('@.documentnumber', 'int'),
[inv].[ref].value('@.invoicebalance', 'money')
FROM @.xml.nodes('/root/InvoiceBalanceData') [inv]([ref])
See SQL Server BOL as well.
-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @.docHandle int
DECLARE @.xmlDocument nvarchar(max) -- or xml type
SET @.xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@.docHandle, N'/ROOT/Customers')
WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@.docHandle, N'//Orders')
WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@.docHandle, N'/ROOT/Customers/Orders') WITH
(CustomerID nchar(5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @.docHandle
"Larry Bud" <larrybud2002@.yahoo.com> wrote in message
news:6ebab460-5eda-4c48-a4be-1ef478130ea3@.i29g2000prf.googlegroups.com...
> On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> I should add that after I attempt to import the XML, the script
> finishes successfully (this is from a DTS package), but no rows are
> imported into the table.

Help with SQL Mapping

First time trying to import a pretty simple XML file into a SQL
table. I've been reading annotated XSD file docs for the past 3 hours
and it's just not clear to me. Here's my XML
<?xml version="1.0" standalone="yes"?>
<InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>12345</documentnumber>
<invoicebalance>99.99</invoicebalance>
<invoicenumber>INV123</invoicenumber>
<invoicetrantype>FS</invoicetrantype>
</InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>444</documentnumber>
<invoicebalance>88.88</invoicebalance>
<invoicenumber>INV345</invoicenumber>
<invoicetrantype>AB</invoicetrantype>
</InvoiceBalanceData>
</InvoiceBalanceData>
Can someone help me with my annotated XSD file? tia.On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> First time trying to import a pretty simple XML file into a SQL
> table. I've been reading annotated XSD file docs for the past 3 hours
> and it's just not clear to me. Here's my XML
> <?xml version="1.0" standalone="yes"?>
> <InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>12345</documentnumber>
> <invoicebalance>99.99</invoicebalance>
> <invoicenumber>INV123</invoicenumber>
> <invoicetrantype>FS</invoicetrantype>
> </InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>444</documentnumber>
> <invoicebalance>88.88</invoicebalance>
> <invoicenumber>INV345</invoicenumber>
> <invoicetrantype>AB</invoicetrantype>
> </InvoiceBalanceData>
> </InvoiceBalanceData>
> Can someone help me with my annotated XSD file? tia.
I should add that after I attempt to import the XML, the script
finishes successfully (this is from a DTS package), but no rows are
imported into the table.|||I would process it as attribute centric
declare @.xml xml
SET @.xml =
'<root>
<InvoiceBalanceData documentnumber="12345" invoicebalance="99.99"
invoicenumber="INV123" invoicetrantype="FS" />
<InvoiceBalanceData documentnumber="444" invoicebalance="88.88"
invoicenumber="INV345" invoicetrantype="AB" />
</root>'
select @.xml
SELECT
[inv].[ref].value('@.documentnumber', 'int'),
[inv].[ref].value('@.invoicebalance', 'money')
FROM @.xml.nodes('/root/InvoiceBalanceData') [inv]([ref])
See SQL Server BOL as well.
-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @.docHandle int
DECLARE @.xmlDocument nvarchar(max) -- or xml type
SET @.xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@.docHandle, N'/ROOT/Customers')
WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@.docHandle, N'//Orders')
WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@.docHandle, N'/ROOT/Customers/Orders') WITH
(CustomerID nchar(5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @.docHandle
"Larry Bud" <larrybud2002@.yahoo.com> wrote in message
news:6ebab460-5eda-4c48-a4be-1ef478130ea3@.i29g2000prf.googlegroups.com...
> On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> I should add that after I attempt to import the XML, the script
> finishes successfully (this is from a DTS package), but no rows are
> imported into the table.

Help with reading datetime with DATEPART

I was hoping someone could help me with the sql syntax in trying to return the date from a datetime value. I'm trying to get the month and day and year from a datetime value in the database but I keep getting a token error. This is the code I'm using to try to read the date, from everything I've read for sql, it should work but it doesn't.

Dim sql As String = "SELECT * FROM People WHERE DATEPART(month, dtime) = '" & _
DateTime.Month & "' & DATEPART(year, dtime) = '" & DateTime.Year & '"

Dim Sqlreader As SqlCeDataReader = cmd.ExecuteReader

The error I get is:
There was an error parsing the query. [ Token line number = 1,Token line offset = 82,Token in error = = ]

It doesn't seem to recognize the second DATEPART search and the = sign is a syntax error.

What am I doing wrong here?

in the "& DATEPART(year, dtime)" part, replace "&" with "and"

regards

|||Hmmm, I tried the '&' symbol and also tried 'AND' but not a lowercase 'and'.
Thanks.

crt

Help with reading database.

I have this code that I hacked together from someone else's example. I kind of understand how it works. I just don't know if it will and i am not in a location right now to check. I was wondering if I did this correctly first, second how can it improve and should i do something different. Basically i just want to check the password in a database. I am passing the username and password to this function from another functio

private bool authUser(string UserName, string Password)
{
string connectionString = ConfigurationSettings.AppSettings["DBConnectionString"];

SqlConnection DBConnection = new SqlConnection(connectionString);

bool result = false;

DBConnection.open()
SqlCommand checkCommand = new SqlCommand("SELECT password FROM Users WHERE userName='" + Password + "', DBConnection)
SqlDataReader checkDataReader = checkCommand.ExecuteReader();

if(checkDataReader.GetString(0) == Password)
{
result = true;
}
else
{
result = false;
}
checkDataReader.Close();
DBConnection.Close();

return result;
}

Thank you
Buddy LindseyNo. It won't work.
You're using the Password in your SQL statement, not the username. So unless the users are really silly and use their usernames as their passwords, it's going to fail.
More seriously, using string concatenation to build query strings represents one of the biggest security disasters on the planet. Never, ever do it. Just search the web for "SQL Injection attack" to see what I mean.
You should also ponder whether == with string comparisons is case sensitive or not.
You can also leak connection resources because you're not using try {} finally {} blocks or the C# using(){} wrapper.
And I'm really hoping that the password is protected in some way (by hashing or encryption), so that the password that is passed into this method is not the plain text password as entered by a user.
|||Yep that definetly won't work,
I agree with DMW, people who write code like that should be shot! :-)
At it's most basic that function may work , apart from the obvious mistake of querying the Password as the Username,
I also convert my string comparisons to .toupper or .tolower , just get rid of casing issues.
on Passwords I always enter them into the DB encrypted, and basically compare the encrypted text.
I would never do this in Code, as I would rather do this kind of operation in the Stored Proc, and let the SP deliver the result.
Inline SQL is so Passe, in a field where we must be moving with the times , this is a no no.
|||That is a little harsh of a response don't you think. Did youtake the time to consider that i may be a new developer and not reallyknow what i am doing.
Anyway, that is why I posted I wanted to dknow if i was doing it rightor not. And when i pass the password to the function it willalready be hashed.
Thanks for the help.
|||

appologies if I came across a little, harsh I did not certainly mean in it in that way,

I was trying to be funny.

I'll stick to code, because I suck at language :-)

|||I agree.
I don't recall advocating that ANY developer ever be shot.
And the reason that I spend my time on this forum is that I'm passionate about passing on what little experience I have to other developers so that they can learn from my mistakes.
So keep posting, and you'll get lot's of support and advice.
Speaking of which, I'll offer one other piece of advice: never, ever use someone else's code (especially not sample code or book code) unless you're really happy with how it works. Most samples, including book samples, don't follow best practice. If they did, the code would be harder to follow and generally much too long to fit in a book. A lot of "book code" (by which I mean conference samples, MSDN samples, my samples) is written to try and explain a learning point, not for commercial use. I know that this sounds daft, but that's the way it is.
The most common thing that is omitted is error handling code (which is critical), and the normal security reviews that would accompany real application development.|||Apology accpeted no hard feelings at all.
yeah one reason I posted was to make sure that I was doing it right. Thank for taking the time to answer.
I have the book code complete so that i can learn better ways of codingI guess i need to pull that sucker out and try to read it again.
Thanks for all the help.
sql