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.