Max Query: 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

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>
Zagreb
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
 1
<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>
Zagreb
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
 2
<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>
> Zagreb
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> 1
> <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>
> Zagreb
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> 2
> <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!
>
>
>

Wednesday, March 7, 2012

Help with OpenXML

help with OPENXML

Help with OPENXML

Max Query

Blog Archive

About Me