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