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 returning a well formed XML for 1 row using XDS

Hi,
I am trying to use SQLXML and have created the template file and schema
files. Everything works fine except that the XML returned is not well
formed i.e. it has the root tag but all the elements are all in one
row. I have tried the root=root option and it is not helping. I have
gone thru all newsgroups / googled it and I am running the latest
version of SQLXML 3.0 SP2 on a SQL2000 Server. It is running on Win XP
w/ SP2. Any help on how to resolve the problem would be appreciated!Can you give a short example of the data you are getting and the one you
would like to get?
Thanks
Michael
<snandaca@.gmail.com> wrote in message
news:1125614930.833716.88890@.g14g2000cwa.googlegroups.com...
> Hi,
> I am trying to use SQLXML and have created the template file and schema
> files. Everything works fine except that the XML returned is not well
> formed i.e. it has the root tag but all the elements are all in one
> row. I have tried the root=root option and it is not helping. I have
> gone thru all newsgroups / googled it and I am running the latest
> version of SQLXML 3.0 SP2 on a SQL2000 Server. It is running on Win XP
> w/ SP2. Any help on how to resolve the problem would be appreciated!
>

Help with returning a well formed XML for 1 row using XDS

Hi,
I am trying to use SQLXML and have created the template file and schema
files. Everything works fine except that the XML returned is not well
formed i.e. it has the root tag but all the elements are all in one
row. I have tried the root=root option and it is not helping. I have
gone thru all newsgroups / googled it and I am running the latest
version of SQLXML 3.0 SP2 on a SQL2000 Server. It is running on Win XP
w/ SP2. Any help on how to resolve the problem would be appreciated!
Can you give a short example of the data you are getting and the one you
would like to get?
Thanks
Michael
<snandaca@.gmail.com> wrote in message
news:1125614930.833716.88890@.g14g2000cwa.googlegro ups.com...
> Hi,
> I am trying to use SQLXML and have created the template file and schema
> files. Everything works fine except that the XML returned is not well
> formed i.e. it has the root tag but all the elements are all in one
> row. I have tried the root=root option and it is not helping. I have
> gone thru all newsgroups / googled it and I am running the latest
> version of SQLXML 3.0 SP2 on a SQL2000 Server. It is running on Win XP
> w/ SP2. Any help on how to resolve the problem would be appreciated!
>

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m simply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.
Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m sim
ply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with OpenXML

Hi,
I am trying to insert an XML document into into 3 tables which match the
hierachy of the xml and which appears to be working. This data could be an
update or an insert so for simplicity I have an initial procedure which
clears down the existing data using a cascading delete. This too appears to
be working.
However, when I check the tables there appears to be hundreds of duplicate
rows ? Has anybody come across anything similar. The tables do have an
identity field within them, which might be causing an issue I guess.
I am using SQL Server 2000 (sp3) on a Windows 2000 Server box.
Grateful for any helpJust realised we are actually using sp4 which might explain the problem, as
I
am using @.mp/@.parentid ?
"Redowl" wrote:

> Hi,
> I am trying to insert an XML document into into 3 tables which match the
> hierachy of the xml and which appears to be working. This data could be a
n
> update or an insert so for simplicity I have an initial procedure which
> clears down the existing data using a cascading delete. This too appears
to
> be working.
> However, when I check the tables there appears to be hundreds of duplicate
> rows ? Has anybody come across anything similar. The tables do have an
> identity field within them, which might be causing an issue I guess.
> I am using SQL Server 2000 (sp3) on a Windows 2000 Server box.
> Grateful for any help

help with OPENXML

I'm having problem with inserting xml into sql server.
I have procedure:
------
CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
------
and then I save xml data from dataset:
------
Dim connStr As String =
"Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated Security=SSPI"
Dim cmd As New SqlXmlCommand(connStr)
cmd.RootTag = "ROOT"
cmd.CommandType = SqlXmlCommandType.Sql
cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT ListaIzvjID,
BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje, Potrazuje, U, Jed, Odj,
Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID = L.ListaIzvjID WHERE
(L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
Dim DA As New SqlXmlAdapter(cmd)
Dim ds As New DataSet
DA.Fill(ds)
ds.WriteXml("D:\BGListaIzvj.xml")
------
and this is the xml file:
------
<?xml version="1.0" standalone="yes"?>
<ROOT>
<L>
<ListaIzvjID>16</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>1</RedBroj>
<Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
<PocStanje>328.79</PocStanje>
<ZavStanje>143.18</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>01.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>16</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-01-07T00:00:00</Datum>
<Primljen>-</Primljen>
<Opis>plaa za 12/03 za Amaliju Cigler</Opis>
<Dokum>Upl1</Dokum>
<Konto>1009</Konto>
<Duguje>222.4</Duguje>
<Potrazuje>0</Potrazuje>
<U>Zagreb</U>
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
<BR>1</BR>
<Godina2>2004</Godina2>
</BGIzvj>
</L>
<L>
<ListaIzvjID>17</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>2</RedBroj>
<Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
<PocStanje>143.18</PocStanje>
<ZavStanje>1797.79</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>06.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>17</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-06-15T00:00:00</Datum>
<Primljen>gotovina</Primljen>
<Opis>mat. trokove i isplata PN</Opis>
<Dokum>Upl2</Dokum>
<Konto>1009</Konto>
<Duguje>15000</Duguje>
<Potrazuje>0</Potrazuje>
<U>Zagreb</U>
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
<BR>2</BR>
<Godina2>2004</Godina2>
</BGIzvj>
</L>
</ROOT>
------
Now, I'm trying to save that xml into sql database with this:
Dim sr As New StreamReader("d:\BGListaIzvj.xml")
Dim strXML As String
strXML = sr.ReadToEnd
sr.Close()
cmd.Parameters("@.strXML").Value = strXML
SqlConnection1.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
SqlConnection1.Close()
but I'm getting error, XML Parsing error: An Invalid character found in text
content.
I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of xml
file, but it won't work.
And why is that <L> </L> there?
Can somebody help me?
thanks!
Try NTEXT for the type of @.strXML (I assume that the data is UTF-16
encoded).
The L element is there because of your FOR XML query.
Best regards
Michael
"green_eye" <bernard@.form.hr> wrote in message
news:clt9kv$cnq$1@.ls219.htnet.hr...
> I'm having problem with inserting xml into sql server.
> I have procedure:
> ------
> CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ------
>
> and then I save xml data from dataset:
> ------
> Dim connStr As String =
> "Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated
> Security=SSPI"
> Dim cmd As New SqlXmlCommand(connStr)
> cmd.RootTag = "ROOT"
> cmd.CommandType = SqlXmlCommandType.Sql
>
> cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
> L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
> I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
> I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
> I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT
> ListaIzvjID, BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje,
> Potrazuje, U, Jed, Odj, Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID =
> L.ListaIzvjID WHERE (L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
>
> Dim DA As New SqlXmlAdapter(cmd)
> Dim ds As New DataSet
> DA.Fill(ds)
>
> ds.WriteXml("D:\BGListaIzvj.xml")
>
> ------
>
> and this is the xml file:
> ------
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <L>
> <ListaIzvjID>16</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>1</RedBroj>
> <Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
> <PocStanje>328.79</PocStanje>
> <ZavStanje>143.18</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>01.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>16</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-01-07T00:00:00</Datum>
> <Primljen>-</Primljen>
> <Opis>plaa za 12/03 za Amaliju Cigler</Opis>
> <Dokum>Upl1</Dokum>
> <Konto>1009</Konto>
> <Duguje>222.4</Duguje>
> <Potrazuje>0</Potrazuje>
> <U>Zagreb</U>
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> <BR>1</BR>
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> <L>
> <ListaIzvjID>17</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>2</RedBroj>
> <Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
> <PocStanje>143.18</PocStanje>
> <ZavStanje>1797.79</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>06.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>17</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-06-15T00:00:00</Datum>
> <Primljen>gotovina</Primljen>
> <Opis>mat. trokove i isplata PN</Opis>
> <Dokum>Upl2</Dokum>
> <Konto>1009</Konto>
> <Duguje>15000</Duguje>
> <Potrazuje>0</Potrazuje>
> <U>Zagreb</U>
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> <BR>2</BR>
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> </ROOT>
> ------
> Now, I'm trying to save that xml into sql database with this:
>
> Dim sr As New StreamReader("d:\BGListaIzvj.xml")
> Dim strXML As String
> strXML = sr.ReadToEnd
> sr.Close()
>
> cmd.Parameters("@.strXML").Value = strXML
> SqlConnection1.Open()
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> MsgBox(ex.Message)
> End Try
> SqlConnection1.Close()
>
> but I'm getting error, XML Parsing error: An Invalid character found in
> text content.
> I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of
> xml file, but it won't work.
> And why is that <L> </L> there?
>
> Can somebody help me?
>
> thanks!
>
>
|||OK, thanks! It works with nText type, but I have some other problem.
With this procedure:
CREATE PROC sp_insert_BGListaIzvj @.strXML nText
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '//BGIzvj')
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
and this XML file:
<?xml version="1.0" standalone="yes"?>
<ROOT>
<BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="1" Godina2="2004" />
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03" Dokum="Ispl1"
Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="1" Godina2="2004" />
</BGListaIzvj>
<BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18" ZavStanje="1797.79"
Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="2" Godina2="2004" />
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="5" Godina2="2004" />
</BGListaIzvj>
</ROOT>
I get rows in second table (BGIzvj) with NULL values in columns ListaIzvjID,
BlgID and Godina. These three tables are the same as in BGListaIzvj table.
Is that a problem? Is there way to fix this?
Thanks!
|||Hi Bernard
What are the schemas of your relational tables?
Since you get the rows, your path expressions find the nodes that map to
rows, but it looks like that the relative paths implied by the relational
schema does not find any values for the nodes where you get NULL.
This probably means that your Schema names and attribute names do not fit.
Best regards
Michael
"Bernard" <bernard@.form.hr> wrote in message
news:cm0576$r2e$1@.ls219.htnet.hr...
> OK, thanks! It works with nText type, but I have some other problem.
> With this procedure:
> ----
> CREATE PROC sp_insert_BGListaIzvj @.strXML nText
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '//BGIzvj')
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ----
>
> and this XML file:
> ----
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
> Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
> Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
> Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="1" Godina2="2004" />
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03"
> Dokum="Ispl1" Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3"
> Odj="0" Tip="I" BR="1" Godina2="2004" />
> </BGListaIzvj>
> <BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
> Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18"
> ZavStanje="1797.79" Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
> Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
> Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="2" Godina2="2004" />
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
> Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
> Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
> BR="5" Godina2="2004" />
> </BGListaIzvj>
> </ROOT>
> ----
>
> I get rows in second table (BGIzvj) with NULL values in columns
> ListaIzvjID, BlgID and Godina. These three tables are the same as in
> BGListaIzvj table. Is that a problem? Is there way to fix this?
>
> Thanks!
>
>
>