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 Special Characters in Updategram

I'm looking for help and hope you can provide it. I've got an
updategram with special ISO Latin 1 Characters like begin and end
quotes #147 and #148 or copyright character #169. The UpdateGram fails
with the message "400.100 Bad Request, Query not specified". The
UpdateGram works when the characters are removed. If I try to HTML
encode the characters (“ ” or ©) then the updategram
saves but the characters are replaced with '?'.
Any ideas how to get around this?
Hi Todd,
The error message you are seeing is not coming from the Updagram component
and is coming from some other layer of the web application. How are you
sending this to the database? One way to check if something is wrong with
the Updategram is to try to send the Updategram directly through ADO or
managed provider. If that does not work and you see an error then please
post a snippet of the Updategram that you think is causing the problem.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided AS IS, and confers on rights or warranties.
"Todd" <todd_gochenour@.msn.com> wrote in message
news:1110836825.988706.296230@.z14g2000cwz.googlegr oups.com...
> I'm looking for help and hope you can provide it. I've got an
> updategram with special ISO Latin 1 Characters like begin and end
> quotes #147 and #148 or copyright character #169. The UpdateGram fails
> with the message "400.100 Bad Request, Query not specified". The
> UpdateGram works when the characters are removed. If I try to HTML
> encode the characters (“ ” or ©) then the updategram
> saves but the characters are replaced with '?'.
> Any ideas how to get around this?
>
|||The updategram is posted to the SQLXML website. The content of the
updategram is:
<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<ROOT xmlns:updg=3D"urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:before>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test"/>
</updg:before>
<updg:after>
<wwfinaid_Doc updg:id=3D"L0" Instance=3D"User[67792]"
Name=3D"Documents/test" VALUE=3D"Tomorrow's Hope=A9"/>
</updg:after>
</updg:sync>
</ROOT>
Note the curly apostrophe and the copyright symbol. If they are
removed, then the updategram is successful.
|||Also, I have not attempted to submit this updategram through the ADO
interface. It'll take some work to get this setup, and my current
configuration is using the HTTP based interface.

Help with simple insert, how to use primary key?

Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.

i want an unique identifier for each row.

heres my stored procedure:

CREATE PROCEDURE composeMessage

-- Add the parameters for the stored procedure here

@.username varchar(24),

@.sender varchar(24),

@.date dateTime,

@.subject varchar(255),

@.message varchar(2500)

AS

BEGIN

insert into Messages(

"Username",

"Sender",

"Date",

"Subject",

"Message"

)

values (

@.username,

@.sender,

@.date,

@.subject,

@.message

)

END

GO

heres my sqlcreate table:

USE [Messenger]

GO

/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Messages](

[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,

[Date] [datetime] NOT NULL,

[ID] [int] NOT NULL,

CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

As primary keycan't be null, what do i put for primary key for my insert to work?

hope you understand what i mean?

Am i right that i have to set the table designer/identity column to my primary key?

It generates an unique incresing number, so doi i use that?

|||

If you want to have an increasing value, you will have to switch on the identity property on your column.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Help with query....

I have this query which I've left running for around 8 hours and does not
return (but is eating a lot of CPU and DISK IO).
The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
above 25% used, 1.1 disk queue. This server is not being used by anyone
else accept my query.
Integrations_activity has 25million rows, candidate has 1.4 million rows.
Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
using MAXDOP 1 to see if parallelism made a difference and it still ran for
8 hours before I killed it.
The query plan is shown below
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock)
join Candidates (nolock) ON candidates.indnum =
Integrations_Activity.IndNum
AND candidates.indofficenum =
Integrations_Activity.Indofficenum
WHERE Integrations_Activity.typeofactivity = 'CAN' AND
Integrations_Activity.IndNum IS NOT NULL
StmtText
----
----
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock), Candidates (nolock)
Where Integrations_Activity.IndNum IS NOT NULL
And Integrations_Activity.typeofactivity = 'CAN'
And Integrations_Activity.IndNum = Candidates.IndNum
And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
(1 row(s) affected)
StmtText
----
|--Table Update(OBJECT[RMTEST].[dbo].[Integrations_Activity]),
SET[Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Aggregate, HASH[Bmk1000]),
RESIDUAL[Bmk1000]=[Bmk1000])
DEFINE[Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS[Bmk1000]))
|--Hash Match(Inner Join,
HASH[Candidates].[IndNum],
[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
RESIDUAL[Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
[Expr1006]=[Candidates].[IndOfficeNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS[Candidates].[IndNum], [Candidates].[IndOfficeNum]))
| |--Hash Match(Inner Join,
HASH[Bmk1002])=([Bmk1002]), RESIDUAL[Bmk1002]=[Bmk1002]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS[Bmk1002]))
| | |--Index
Scan(OBJECT[RMTEST].[dbo].[Candidates].[pk_Candidates]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS[Bmk1002]))
| |--Index
Scan(OBJECT[RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS[Integrations_Activity].[IndNum], [Expr1006]))
|--Compute
Scalar(DEFINE[Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
|--Table
Scan(OBJECT[RMTEST].[dbo].[Integrations_Activity]),
WHERE[Integrations_Activity].[IndNum]<>NULL AND
[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
(15 row(s) affected)
Answered in .programming. Please don't multi-post as we can't guess
everywhere where you posted the question.
Regards
Mike
"Paul" wrote:

> I have this query which I've left running for around 8 hours and does not
> return (but is eating a lot of CPU and DISK IO).
> The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
> 8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
> above 25% used, 1.1 disk queue. This server is not being used by anyone
> else accept my query.
> Integrations_activity has 25million rows, candidate has 1.4 million rows.
> Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
> using MAXDOP 1 to see if parallelism made a difference and it still ran for
> 8 hours before I killed it.
> The query plan is shown below
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum =
> Integrations_Activity.IndNum
> AND candidates.indofficenum =
> Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
> Integrations_Activity.IndNum IS NOT NULL
>
> StmtText
> ----
> ----
> ----
> ----
> ----
>
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock), Candidates (nolock)
> Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
> (1 row(s) affected)
> StmtText
> ----
> ----
> ----
> |--Table Update(OBJECT[RMTEST].[dbo].[Integrations_Activity]),
> SET[Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH[Bmk1000]),
> RESIDUAL[Bmk1000]=[Bmk1000])
> DEFINE[Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS[Bmk1000]))
> |--Hash Match(Inner Join,
> HASH[Candidates].[IndNum],
> [Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
> RESIDUAL[Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
> [Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS[Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
> HASH[Bmk1002])=([Bmk1002]), RESIDUAL[Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS[Bmk1002]))
> | | |--Index
> Scan(OBJECT[RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS[Bmk1002]))
> | |--Index
> Scan(OBJECT[RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS[Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
> Scalar(DEFINE[Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
> Scan(OBJECT[RMTEST].[dbo].[Integrations_Activity]),
> WHERE[Integrations_Activity].[IndNum]<>NULL AND
> [Integrations_Activity].[typeofactivity]='CAN') ORDERED)
> (15 row(s) affected)
>
>
>
|||Can you post the CREATE TABLE AND CREATE INDEX statements, and the
non-parallel plan? Also, are there any constraints, indexed views, or
other dependent objects?
Do you have any idea how many rows this query will update?
Steve Kass
Drew University
Paul wrote:

>I have this query which I've left running for around 8 hours and does not
>return (but is eating a lot of CPU and DISK IO).
>The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
>8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
>above 25% used, 1.1 disk queue. This server is not being used by anyone
>else accept my query.
>Integrations_activity has 25million rows, candidate has 1.4 million rows.
>Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
>using MAXDOP 1 to see if parallelism made a difference and it still ran for
>8 hours before I killed it.
>The query plan is shown below
>Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum =
>Integrations_Activity.IndNum
> AND candidates.indofficenum =
>Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
>Integrations_Activity.IndNum IS NOT NULL
>
>StmtText
>----
>----
>----
>----
>----
>
>Update Integrations_Activity
>Set CandidateID = Candidates.CandidateID
>From Integrations_Activity (nolock), Candidates (nolock)
>Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
>(1 row(s) affected)
>StmtText
>----
>----
>----
>---
> |--Table Update(OBJECT[RMTEST].[dbo].[Integrations_Activity]),
>SET[Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH[Bmk1000]),
>RESIDUAL[Bmk1000]=[Bmk1000])
>DEFINE[Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
>COLUMNS[Bmk1000]))
> |--Hash Match(Inner Join,
>HASH[Candidates].[IndNum],
>[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
>RESIDUAL[Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
>[Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS[Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
>HASH[Bmk1002])=([Bmk1002]), RESIDUAL[Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS[Bmk1002]))
> | | |--Index
>Scan(OBJECT[RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS[Bmk1002]))
> | |--Index
>Scan(OBJECT[RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS[Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
>Scalar(DEFINE[Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
>Scan(OBJECT[RMTEST].[dbo].[Integrations_Activity]),
>WHERE[Integrations_Activity].[IndNum]<>NULL AND
>[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
>(15 row(s) affected)
>
>
>