I am looking for some of the obvious questions one needs to ask from the
devs,etc. to help Operations be ready to be in a position to understand some
of the new requirements coming in and be prepared from a datbase
perspective.. such as DB size, capacity,etc..
Just naive to this and wanted to see what you have in place
ThanksHmm. Do you have any specific items you want to cover? Operations can
cover a *huge* range of functions depending on your industry. MS and other
vendors print up tons of White Papers that are designed to tell you the
questions you *should be* asking; and of course the answers always tend to
favor their specific products. But that's marketing disguised as
scholarship for you...
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>I am looking for some of the obvious questions one needs to ask from the
>devs,etc. to help Operations be ready to be in a position to understand
>some of the new requirements coming in and be prepared from a datbase
>perspective.. such as DB size, capacity,etc..
> Just naive to this and wanted to see what you have in place
> Thanks
>|||Do you have links to those white papers pertaining to SQL Server.
"Mike C#" <xxx@.yyy.com> wrote in message news:Q1Qbg.1364$%02.733@.fe09.lga...
> Hmm. Do you have any specific items you want to cover? Operations can
> cover a *huge* range of functions depending on your industry. MS and
> other vendors print up tons of White Papers that are designed to tell you
> the questions you *should be* asking; and of course the answers always
> tend to favor their specific products. But that's marketing disguised as
> scholarship for you...
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>|||http://www.microsoft.com, man. Here are a few (pulled by searching
microsoft.com for "sql white paper"):
http://www.microsoft.com/sql/techin...nt.msp
x
http://www.microsoft.com/technet/pr...lan/ssmsam.mspx
http://download.microsoft.com/downl...verInRetail.doc
http://www.microsoft.com/sql/techin...hy-upgrade.mspx
http://www.microsoft.com/technet/pr...5/sqlupgrd.mspx
http://www.microsoft.com/sql/soluti...de/default.mspx (links to other
white papers, benchmarks, etc.)
http://www.microsoft.com/technet/pr...in/sqlydba.mspx
http://www.microsoft.com/technet/pr...te/dwsqlsy.mspx
http://support.microsoft.com/defaul...kb;en-us;323739
http://www.microsoft.com/sql/howtob...rlicensing.mspx
http://www.microsoft.com/sql/howtobuy/default.mspx
http://www.microsoft.com/sql/soluti...emigratewp.mspx
http://download.microsoft.com/downl...oSQL2005TWP.doc
http://www.microsoft.com/sql/techno...ty/default.mspx
(links to white papers, etc.)
http://www.microsoft.com/sql/prodin...bm/default.mspx
http://www.microsoft.com/sql/prodin...le/default.mspx
http://www.microsoft.com/sql/prodin...cle/oracle.mspx (links)
http://www.microsoft.com/sql/solutions/edm/default.mspx (links)
http://www.microsoft.com/technet/it...it/default.mspx (links)
http://www.microsoft.com/sql/prodin...ailability.mspx
http://support.microsoft.com/?id=321518
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
There are about 9,700+ pages on microsoft.com that reference "sql white
paper". These results are from the first 80 pages. You might want to
narrow down your results a bit by deciding exactly what issues are most
important to you and narrowing your searches for information on those
topics.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O8hVqaJfGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Do you have links to those white papers pertaining to SQL Server.
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:Q1Qbg.1364$%02.733@.fe09.lga...
>
Showing posts with label document. Show all posts
Showing posts with label document. Show all posts
Wednesday, March 7, 2012
Operations Spec Document
I am looking for some of the obvious questions one needs to ask from the
devs,etc. to help Operations be ready to be in a position to understand some
of the new requirements coming in and be prepared from a datbase
perspective.. such as DB size, capacity,etc..
Just naive to this and wanted to see what you have in place
ThanksHmm. Do you have any specific items you want to cover? Operations can
cover a *huge* range of functions depending on your industry. MS and other
vendors print up tons of White Papers that are designed to tell you the
questions you *should be* asking; and of course the answers always tend to
favor their specific products. But that's marketing disguised as
scholarship for you...
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>I am looking for some of the obvious questions one needs to ask from the
>devs,etc. to help Operations be ready to be in a position to understand
>some of the new requirements coming in and be prepared from a datbase
>perspective.. such as DB size, capacity,etc..
> Just naive to this and wanted to see what you have in place
> Thanks
>|||Do you have links to those white papers pertaining to SQL Server.
"Mike C#" <xxx@.yyy.com> wrote in message news:Q1Qbg.1364$%02.733@.fe09.lga...
> Hmm. Do you have any specific items you want to cover? Operations can
> cover a *huge* range of functions depending on your industry. MS and
> other vendors print up tons of White Papers that are designed to tell you
> the questions you *should be* asking; and of course the answers always
> tend to favor their specific products. But that's marketing disguised as
> scholarship for you...
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>>I am looking for some of the obvious questions one needs to ask from the
>>devs,etc. to help Operations be ready to be in a position to understand
>>some of the new requirements coming in and be prepared from a datbase
>>perspective.. such as DB size, capacity,etc..
>> Just naive to this and wanted to see what you have in place
>> Thanks
>|||http://www.microsoft.com, man. Here are a few (pulled by searching
microsoft.com for "sql white paper"):
http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/ssmsam.mspx
http://download.microsoft.com/download/e/2/b/e2b4227d-54a9-4c09-82b8-892f98d73b51/SQLServerInRetail.doc
http://www.microsoft.com/sql/techinfo/whitepapers/why-upgrade.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
http://www.microsoft.com/sql/solutions/upgrade/default.mspx (links to other
white papers, benchmarks, etc.)
http://www.microsoft.com/technet/prodtechnol/sql/2005/maintain/sqlydba.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/dwsqlsy.mspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;323739
http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.mspx
http://www.microsoft.com/sql/howtobuy/default.mspx
http://www.microsoft.com/sql/solutions/ssm/oraclemigratewp.mspx
http://download.microsoft.com/download/1/a/7/1a7a9faa-2f90-4e56-a4b7-977f1ce96dd5/MovingToSQL2005TWP.doc
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
(links to white papers, etc.)
http://www.microsoft.com/sql/prodinfo/compare/ibm/default.mspx
http://www.microsoft.com/sql/prodinfo/compare/oracle/default.mspx
http://www.microsoft.com/sql/prodinfo/compare/oracle/oracle.mspx (links)
http://www.microsoft.com/sql/solutions/edm/default.mspx (links)
http://www.microsoft.com/technet/itsolutions/msit/default.mspx (links)
http://www.microsoft.com/sql/prodinfo/compare/oracle/dbavailability.mspx
http://support.microsoft.com/?id=321518
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
There are about 9,700+ pages on microsoft.com that reference "sql white
paper". These results are from the first 80 pages. You might want to
narrow down your results a bit by deciding exactly what issues are most
important to you and narrowing your searches for information on those
topics.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O8hVqaJfGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Do you have links to those white papers pertaining to SQL Server.
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:Q1Qbg.1364$%02.733@.fe09.lga...
>> Hmm. Do you have any specific items you want to cover? Operations can
>> cover a *huge* range of functions depending on your industry. MS and
>> other vendors print up tons of White Papers that are designed to tell you
>> the questions you *should be* asking; and of course the answers always
>> tend to favor their specific products. But that's marketing disguised as
>> scholarship for you...
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>>I am looking for some of the obvious questions one needs to ask from the
>>devs,etc. to help Operations be ready to be in a position to understand
>>some of the new requirements coming in and be prepared from a datbase
>>perspective.. such as DB size, capacity,etc..
>> Just naive to this and wanted to see what you have in place
>> Thanks
>>
>
devs,etc. to help Operations be ready to be in a position to understand some
of the new requirements coming in and be prepared from a datbase
perspective.. such as DB size, capacity,etc..
Just naive to this and wanted to see what you have in place
ThanksHmm. Do you have any specific items you want to cover? Operations can
cover a *huge* range of functions depending on your industry. MS and other
vendors print up tons of White Papers that are designed to tell you the
questions you *should be* asking; and of course the answers always tend to
favor their specific products. But that's marketing disguised as
scholarship for you...
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>I am looking for some of the obvious questions one needs to ask from the
>devs,etc. to help Operations be ready to be in a position to understand
>some of the new requirements coming in and be prepared from a datbase
>perspective.. such as DB size, capacity,etc..
> Just naive to this and wanted to see what you have in place
> Thanks
>|||Do you have links to those white papers pertaining to SQL Server.
"Mike C#" <xxx@.yyy.com> wrote in message news:Q1Qbg.1364$%02.733@.fe09.lga...
> Hmm. Do you have any specific items you want to cover? Operations can
> cover a *huge* range of functions depending on your industry. MS and
> other vendors print up tons of White Papers that are designed to tell you
> the questions you *should be* asking; and of course the answers always
> tend to favor their specific products. But that's marketing disguised as
> scholarship for you...
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>>I am looking for some of the obvious questions one needs to ask from the
>>devs,etc. to help Operations be ready to be in a position to understand
>>some of the new requirements coming in and be prepared from a datbase
>>perspective.. such as DB size, capacity,etc..
>> Just naive to this and wanted to see what you have in place
>> Thanks
>|||http://www.microsoft.com, man. Here are a few (pulled by searching
microsoft.com for "sql white paper"):
http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/ssmsam.mspx
http://download.microsoft.com/download/e/2/b/e2b4227d-54a9-4c09-82b8-892f98d73b51/SQLServerInRetail.doc
http://www.microsoft.com/sql/techinfo/whitepapers/why-upgrade.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
http://www.microsoft.com/sql/solutions/upgrade/default.mspx (links to other
white papers, benchmarks, etc.)
http://www.microsoft.com/technet/prodtechnol/sql/2005/maintain/sqlydba.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/dwsqlsy.mspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;323739
http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.mspx
http://www.microsoft.com/sql/howtobuy/default.mspx
http://www.microsoft.com/sql/solutions/ssm/oraclemigratewp.mspx
http://download.microsoft.com/download/1/a/7/1a7a9faa-2f90-4e56-a4b7-977f1ce96dd5/MovingToSQL2005TWP.doc
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
(links to white papers, etc.)
http://www.microsoft.com/sql/prodinfo/compare/ibm/default.mspx
http://www.microsoft.com/sql/prodinfo/compare/oracle/default.mspx
http://www.microsoft.com/sql/prodinfo/compare/oracle/oracle.mspx (links)
http://www.microsoft.com/sql/solutions/edm/default.mspx (links)
http://www.microsoft.com/technet/itsolutions/msit/default.mspx (links)
http://www.microsoft.com/sql/prodinfo/compare/oracle/dbavailability.mspx
http://support.microsoft.com/?id=321518
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
There are about 9,700+ pages on microsoft.com that reference "sql white
paper". These results are from the first 80 pages. You might want to
narrow down your results a bit by deciding exactly what issues are most
important to you and narrowing your searches for information on those
topics.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O8hVqaJfGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Do you have links to those white papers pertaining to SQL Server.
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:Q1Qbg.1364$%02.733@.fe09.lga...
>> Hmm. Do you have any specific items you want to cover? Operations can
>> cover a *huge* range of functions depending on your industry. MS and
>> other vendors print up tons of White Papers that are designed to tell you
>> the questions you *should be* asking; and of course the answers always
>> tend to favor their specific products. But that's marketing disguised as
>> scholarship for you...
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%23E5N7wDfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>>I am looking for some of the obvious questions one needs to ask from the
>>devs,etc. to help Operations be ready to be in a position to understand
>>some of the new requirements coming in and be prepared from a datbase
>>perspective.. such as DB size, capacity,etc..
>> Just naive to this and wanted to see what you have in place
>> Thanks
>>
>
Saturday, February 25, 2012
Operating System Memory. How much?
I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
Operating System Memory. How much?
I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
Operating System Memory. How much?
I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
OPENXML won't return rows
I am trying to insert a row from an XML document into a table. Here is
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
Here is the code to get the data from the document and insert it:
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.
You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegr oups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> Here is the code to get the data from the document and insert it:
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>
|||That was it! Thanks very much, Graeme.
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
Here is the code to get the data from the document and insert it:
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.
You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegr oups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> Here is the code to get the data from the document and insert it:
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>
|||That was it! Thanks very much, Graeme.
OPENXML won't return rows
I am trying to insert a row from an XML document into a table. Here is
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
---
Here is the code to get the data from the document and insert it:
---
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegroups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> ---
> Here is the code to get the data from the document and insert it:
> ---
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>|||That was it! Thanks very much, Graeme.
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
---
Here is the code to get the data from the document and insert it:
---
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegroups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> ---
> Here is the code to get the data from the document and insert it:
> ---
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>|||That was it! Thanks very much, Graeme.
Monday, February 20, 2012
openxml question
Hello,
I read a document from MSDN saying that:
Avoid OPENXML over large XML documents.
Avoid large numbers of concurrent OPENXML statements over XML
documents.
But my question is: how to define large? Is there some suggestion
saying for example that a 100kb xml file will be too big? Or 100
concurrent openxml will be to match for the system?
The document on msdn can be found at:
http://msdn2.microsoft.com/en-us/library/ms998577.aspx
The document was written in 2004. Are the arguments valid for both sql
2000 and 2995?
Many Thanks
JerryYes the arguments are pretty much the same. Other than adding XML data type
support, I'm not aware of any changes to the inner workings of OPENXML. If
you're using SQL 2005, you might try using the nodes() method of the XML
data type to shred your XML data instead.
<DAXU@.hotmail.com> wrote in message
news:7da00930-9135-4ea6-9438-b2276eccdc1e@.h11g2000prf.googlegroups.com...
> Hello,
> I read a document from MSDN saying that:
> Avoid OPENXML over large XML documents.
> Avoid large numbers of concurrent OPENXML statements over XML
> documents.
>
> But my question is: how to define large? Is there some suggestion
> saying for example that a 100kb xml file will be too big? Or 100
> concurrent openxml will be to match for the system?
> The document on msdn can be found at:
> http://msdn2.microsoft.com/en-us/library/ms998577.aspx
> The document was written in 2004. Are the arguments valid for both sql
> 2000 and 2995?
> Many Thanks
> Jerry
>|||On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>Hello,
>I read a document from MSDN saying that:
>Avoid OPENXML over large XML documents.
>Avoid large numbers of concurrent OPENXML statements over XML
>documents.
>
>But my question is: how to define large? Is there some suggestion
>saying for example that a 100kb xml file will be too big? Or 100
>concurrent openxml will be to match for the system?
>The document on msdn can be found at:
>http://msdn2.microsoft.com/en-us/library/ms998577.aspx
>The document was written in 2004. Are the arguments valid for both sql
>2000 and 2995?
No, I think not.
On SQL2005 (!), I've used openxml to process two megabyte files on a
tiny, overloaded laptop, I run five or ten nodes queries to shred it
in two seconds, and that's pretty darned good performance in my book!
Maybe 20mb would be a problem, or 200mb. Assume it does keep
everything in RAM, but tokenized. Also even small systems today do
have gigabytes of RAM, which may be wasn't so true even four years
ago.
J.|||> On SQL2005 (!), I've used openxml to process two megabyte files
Simply out of curiosity, did this out-perform using the xml datatype
to do the inital shred?
Marc|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:knbrn3lgsftdtev6ofsnt3sv9fosai8oh5@.
4ax.com...
> On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>
> No, I think not.
> On SQL2005 (!), I've used openxml to process two megabyte files on a
> tiny, overloaded laptop, I run five or ten nodes queries to shred it
> in two seconds, and that's pretty darned good performance in my book!
> Maybe 20mb would be a problem, or 200mb. Assume it does keep
> everything in RAM, but tokenized. Also even small systems today do
> have gigabytes of RAM, which may be wasn't so true even four years
> ago.
One of the problems with the legacy methods of shredding XML using the
COM-based stored procs and OPENXML is that it automatically assigned 1/8th
of your SQL Server's total memory to the XML cache. Unfortunately this is a
limitation of the MSXML parser used by SQL Server to fulfill OPENXML
requests. According to this article you could potentially run out of memory
if you prepare too many XML documents at once without releasing some.|||FYI, here's the SQL 2005 article detailing the 1/8th memory issue:
http://msdn2.microsoft.com/en-us/library/ms187367.aspx. This issue affects
SQL Server 2005 as well as SQL Server 2000. This means that if your SQL
Server has 2 GB assigned to it, that 2 MB XML file you're processing just
got 250 MB assigned to it until you destroy the COM object with
sp_xml_removedocument.
That's quite a hefty price to pay to process a 2 MB file.|||"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz). On
a simple 1 MB XML document I came up with the following performance:
- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds
OPENXML appears to be slightly faster for simple XML documents, although it
reserves 1/8th of the SQL Server memory regardless of the size of the XML
document. I didn't test it with more complex documents and paths, like
documents with multiple namespaces, and several levels of nesting, etc.
When you are shredding XML documents stored in a column, the nodes() method
with a primary XML index is considerably faster than either of the other two
methods.|||Typo, 100 KB XML document, not 1 MB.
"Mike C#" <xyz@.xyz.com> wrote in message
news:eLL1CpKUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "Marc Gravell" <marc.gravell@.gmail.com> wrote in message
> news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
> On a simple 1 MB XML document I came up with the following performance:
> - shredding an XML type variable with nodes() method 1,000 times took 77
> seconds
> - shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
> - shredding an XML type column with nodes() method and primary XML index
> 1,000 times took 42 seconds
> OPENXML appears to be slightly faster for simple XML documents, although
> it reserves 1/8th of the SQL Server memory regardless of the size of the
> XML document. I didn't test it with more complex documents and paths,
> like documents with multiple namespaces, and several levels of nesting,
> etc. When you are shredding XML documents stored in a column, the nodes()
> method with a primary XML index is considerably faster than either of the
> other two methods.
>|||My pardon, I was using openrowset('bulk ...'), and I guess that is using the
XML datatype for shredding.
Josh
"Marc Gravell" wrote:
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
>|||"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:4F59EC99-46F2-4F48-99CF-F453DCD947C1@.microsoft.com...
> My pardon, I was using openrowset('bulk ...'), and I guess that is using
> the
> XML datatype for shredding.
?
Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type variable
or column? I ask because casting character/binary data to XML and shredding
it to relational format are two completely different tasks.
I read a document from MSDN saying that:
Avoid OPENXML over large XML documents.
Avoid large numbers of concurrent OPENXML statements over XML
documents.
But my question is: how to define large? Is there some suggestion
saying for example that a 100kb xml file will be too big? Or 100
concurrent openxml will be to match for the system?
The document on msdn can be found at:
http://msdn2.microsoft.com/en-us/library/ms998577.aspx
The document was written in 2004. Are the arguments valid for both sql
2000 and 2995?
Many Thanks
JerryYes the arguments are pretty much the same. Other than adding XML data type
support, I'm not aware of any changes to the inner workings of OPENXML. If
you're using SQL 2005, you might try using the nodes() method of the XML
data type to shred your XML data instead.
<DAXU@.hotmail.com> wrote in message
news:7da00930-9135-4ea6-9438-b2276eccdc1e@.h11g2000prf.googlegroups.com...
> Hello,
> I read a document from MSDN saying that:
> Avoid OPENXML over large XML documents.
> Avoid large numbers of concurrent OPENXML statements over XML
> documents.
>
> But my question is: how to define large? Is there some suggestion
> saying for example that a 100kb xml file will be too big? Or 100
> concurrent openxml will be to match for the system?
> The document on msdn can be found at:
> http://msdn2.microsoft.com/en-us/library/ms998577.aspx
> The document was written in 2004. Are the arguments valid for both sql
> 2000 and 2995?
> Many Thanks
> Jerry
>|||On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>Hello,
>I read a document from MSDN saying that:
>Avoid OPENXML over large XML documents.
>Avoid large numbers of concurrent OPENXML statements over XML
>documents.
>
>But my question is: how to define large? Is there some suggestion
>saying for example that a 100kb xml file will be too big? Or 100
>concurrent openxml will be to match for the system?
>The document on msdn can be found at:
>http://msdn2.microsoft.com/en-us/library/ms998577.aspx
>The document was written in 2004. Are the arguments valid for both sql
>2000 and 2995?
No, I think not.
On SQL2005 (!), I've used openxml to process two megabyte files on a
tiny, overloaded laptop, I run five or ten nodes queries to shred it
in two seconds, and that's pretty darned good performance in my book!
Maybe 20mb would be a problem, or 200mb. Assume it does keep
everything in RAM, but tokenized. Also even small systems today do
have gigabytes of RAM, which may be wasn't so true even four years
ago.
J.|||> On SQL2005 (!), I've used openxml to process two megabyte files
Simply out of curiosity, did this out-perform using the xml datatype
to do the inital shred?
Marc|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:knbrn3lgsftdtev6ofsnt3sv9fosai8oh5@.
4ax.com...
> On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>
> No, I think not.
> On SQL2005 (!), I've used openxml to process two megabyte files on a
> tiny, overloaded laptop, I run five or ten nodes queries to shred it
> in two seconds, and that's pretty darned good performance in my book!
> Maybe 20mb would be a problem, or 200mb. Assume it does keep
> everything in RAM, but tokenized. Also even small systems today do
> have gigabytes of RAM, which may be wasn't so true even four years
> ago.
One of the problems with the legacy methods of shredding XML using the
COM-based stored procs and OPENXML is that it automatically assigned 1/8th
of your SQL Server's total memory to the XML cache. Unfortunately this is a
limitation of the MSXML parser used by SQL Server to fulfill OPENXML
requests. According to this article you could potentially run out of memory
if you prepare too many XML documents at once without releasing some.|||FYI, here's the SQL 2005 article detailing the 1/8th memory issue:
http://msdn2.microsoft.com/en-us/library/ms187367.aspx. This issue affects
SQL Server 2005 as well as SQL Server 2000. This means that if your SQL
Server has 2 GB assigned to it, that 2 MB XML file you're processing just
got 250 MB assigned to it until you destroy the COM object with
sp_xml_removedocument.
That's quite a hefty price to pay to process a 2 MB file.|||"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz). On
a simple 1 MB XML document I came up with the following performance:
- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds
OPENXML appears to be slightly faster for simple XML documents, although it
reserves 1/8th of the SQL Server memory regardless of the size of the XML
document. I didn't test it with more complex documents and paths, like
documents with multiple namespaces, and several levels of nesting, etc.
When you are shredding XML documents stored in a column, the nodes() method
with a primary XML index is considerably faster than either of the other two
methods.|||Typo, 100 KB XML document, not 1 MB.
"Mike C#" <xyz@.xyz.com> wrote in message
news:eLL1CpKUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "Marc Gravell" <marc.gravell@.gmail.com> wrote in message
> news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
> On a simple 1 MB XML document I came up with the following performance:
> - shredding an XML type variable with nodes() method 1,000 times took 77
> seconds
> - shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
> - shredding an XML type column with nodes() method and primary XML index
> 1,000 times took 42 seconds
> OPENXML appears to be slightly faster for simple XML documents, although
> it reserves 1/8th of the SQL Server memory regardless of the size of the
> XML document. I didn't test it with more complex documents and paths,
> like documents with multiple namespaces, and several levels of nesting,
> etc. When you are shredding XML documents stored in a column, the nodes()
> method with a primary XML index is considerably faster than either of the
> other two methods.
>|||My pardon, I was using openrowset('bulk ...'), and I guess that is using the
XML datatype for shredding.
Josh
"Marc Gravell" wrote:
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
>|||"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:4F59EC99-46F2-4F48-99CF-F453DCD947C1@.microsoft.com...
> My pardon, I was using openrowset('bulk ...'), and I guess that is using
> the
> XML datatype for shredding.
?
Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type variable
or column? I ask because casting character/binary data to XML and shredding
it to relational format are two completely different tasks.
openxml question
Hello,
I read a document from MSDN saying that:
Avoid OPENXML over large XML documents.
Avoid large numbers of concurrent OPENXML statements over XML
documents.
But my question is: how to define large? Is there some suggestion
saying for example that a 100kb xml file will be too big? Or 100
concurrent openxml will be to match for the system?
The document on msdn can be found at:
http://msdn2.microsoft.com/en-us/library/ms998577.aspx
The document was written in 2004. Are the arguments valid for both sql
2000 and 2995?
Many Thanks
Jerry
Yes the arguments are pretty much the same. Other than adding XML data type
support, I'm not aware of any changes to the inner workings of OPENXML. If
you're using SQL 2005, you might try using the nodes() method of the XML
data type to shred your XML data instead.
<DAXU@.hotmail.com> wrote in message
news:7da00930-9135-4ea6-9438-b2276eccdc1e@.h11g2000prf.googlegroups.com...
> Hello,
> I read a document from MSDN saying that:
> Avoid OPENXML over large XML documents.
> Avoid large numbers of concurrent OPENXML statements over XML
> documents.
>
> But my question is: how to define large? Is there some suggestion
> saying for example that a 100kb xml file will be too big? Or 100
> concurrent openxml will be to match for the system?
> The document on msdn can be found at:
> http://msdn2.microsoft.com/en-us/library/ms998577.aspx
> The document was written in 2004. Are the arguments valid for both sql
> 2000 and 2995?
> Many Thanks
> Jerry
>
|||On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>Hello,
>I read a document from MSDN saying that:
>Avoid OPENXML over large XML documents.
>Avoid large numbers of concurrent OPENXML statements over XML
>documents.
>
>But my question is: how to define large? Is there some suggestion
>saying for example that a 100kb xml file will be too big? Or 100
>concurrent openxml will be to match for the system?
>The document on msdn can be found at:
>http://msdn2.microsoft.com/en-us/library/ms998577.aspx
>The document was written in 2004. Are the arguments valid for both sql
>2000 and 2995?
No, I think not.
On SQL2005 (!), I've used openxml to process two megabyte files on a
tiny, overloaded laptop, I run five or ten nodes queries to shred it
in two seconds, and that's pretty darned good performance in my book!
Maybe 20mb would be a problem, or 200mb. Assume it does keep
everything in RAM, but tokenized. Also even small systems today do
have gigabytes of RAM, which may be wasn't so true even four years
ago.
J.
|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:knbrn3lgsftdtev6ofsnt3sv9fosai8oh5@.4ax.com...
> On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>
> No, I think not.
> On SQL2005 (!), I've used openxml to process two megabyte files on a
> tiny, overloaded laptop, I run five or ten nodes queries to shred it
> in two seconds, and that's pretty darned good performance in my book!
> Maybe 20mb would be a problem, or 200mb. Assume it does keep
> everything in RAM, but tokenized. Also even small systems today do
> have gigabytes of RAM, which may be wasn't so true even four years
> ago.
One of the problems with the legacy methods of shredding XML using the
COM-based stored procs and OPENXML is that it automatically assigned 1/8th
of your SQL Server's total memory to the XML cache. Unfortunately this is a
limitation of the MSXML parser used by SQL Server to fulfill OPENXML
requests. According to this article you could potentially run out of memory
if you prepare too many XML documents at once without releasing some.
|||FYI, here's the SQL 2005 article detailing the 1/8th memory issue:
http://msdn2.microsoft.com/en-us/library/ms187367.aspx. This issue affects
SQL Server 2005 as well as SQL Server 2000. This means that if your SQL
Server has 2 GB assigned to it, that 2 MB XML file you're processing just
got 250 MB assigned to it until you destroy the COM object with
sp_xml_removedocument.
That's quite a hefty price to pay to process a 2 MB file.
|||"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz). On
a simple 1 MB XML document I came up with the following performance:
- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds
OPENXML appears to be slightly faster for simple XML documents, although it
reserves 1/8th of the SQL Server memory regardless of the size of the XML
document. I didn't test it with more complex documents and paths, like
documents with multiple namespaces, and several levels of nesting, etc.
When you are shredding XML documents stored in a column, the nodes() method
with a primary XML index is considerably faster than either of the other two
methods.
|||Typo, 100 KB XML document, not 1 MB.
"Mike C#" <xyz@.xyz.com> wrote in message
news:eLL1CpKUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "Marc Gravell" <marc.gravell@.gmail.com> wrote in message
> news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
> On a simple 1 MB XML document I came up with the following performance:
> - shredding an XML type variable with nodes() method 1,000 times took 77
> seconds
> - shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
> - shredding an XML type column with nodes() method and primary XML index
> 1,000 times took 42 seconds
> OPENXML appears to be slightly faster for simple XML documents, although
> it reserves 1/8th of the SQL Server memory regardless of the size of the
> XML document. I didn't test it with more complex documents and paths,
> like documents with multiple namespaces, and several levels of nesting,
> etc. When you are shredding XML documents stored in a column, the nodes()
> method with a primary XML index is considerably faster than either of the
> other two methods.
>
|||My pardon, I was using openrowset('bulk ...'), and I guess that is using the
XML datatype for shredding.
Josh
"Marc Gravell" wrote:
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
>
|||"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:4F59EC99-46F2-4F48-99CF-F453DCD947C1@.microsoft.com...
> My pardon, I was using openrowset('bulk ...'), and I guess that is using
> the
> XML datatype for shredding.
?
Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type variable
or column? I ask because casting character/binary data to XML and shredding
it to relational format are two completely different tasks.
|||On Mon, 7 Jan 2008 21:32:31 -0500, "Mike C#" <xyz@.xyz.com> wrote:
>Are you actually shredding the XML data after you load it using
>openrowset(...) or are you actually just storing it an an XML type variable
>or column? I ask because casting character/binary data to XML and shredding
>it to relational format are two completely different tasks.
Cast it to XML variable with schema to validate via XSD.
Then pass it to SP as XML with no schema, shred it there with four to
six queries mostly cross applies.
Only reason I use the unschema'd XML in the SP is so I can change the
XSD without first dropping the SP. I timed it both ways, seems to
make very little difference. The shredding is indecently fast. Takes
two seconds to shred into table vars, about ten seconds to write rows
to database.
openrowset used like this doesn't do that 1/8 of RAM, does it?
Thanks.
J.
I read a document from MSDN saying that:
Avoid OPENXML over large XML documents.
Avoid large numbers of concurrent OPENXML statements over XML
documents.
But my question is: how to define large? Is there some suggestion
saying for example that a 100kb xml file will be too big? Or 100
concurrent openxml will be to match for the system?
The document on msdn can be found at:
http://msdn2.microsoft.com/en-us/library/ms998577.aspx
The document was written in 2004. Are the arguments valid for both sql
2000 and 2995?
Many Thanks
Jerry
Yes the arguments are pretty much the same. Other than adding XML data type
support, I'm not aware of any changes to the inner workings of OPENXML. If
you're using SQL 2005, you might try using the nodes() method of the XML
data type to shred your XML data instead.
<DAXU@.hotmail.com> wrote in message
news:7da00930-9135-4ea6-9438-b2276eccdc1e@.h11g2000prf.googlegroups.com...
> Hello,
> I read a document from MSDN saying that:
> Avoid OPENXML over large XML documents.
> Avoid large numbers of concurrent OPENXML statements over XML
> documents.
>
> But my question is: how to define large? Is there some suggestion
> saying for example that a 100kb xml file will be too big? Or 100
> concurrent openxml will be to match for the system?
> The document on msdn can be found at:
> http://msdn2.microsoft.com/en-us/library/ms998577.aspx
> The document was written in 2004. Are the arguments valid for both sql
> 2000 and 2995?
> Many Thanks
> Jerry
>
|||On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>Hello,
>I read a document from MSDN saying that:
>Avoid OPENXML over large XML documents.
>Avoid large numbers of concurrent OPENXML statements over XML
>documents.
>
>But my question is: how to define large? Is there some suggestion
>saying for example that a 100kb xml file will be too big? Or 100
>concurrent openxml will be to match for the system?
>The document on msdn can be found at:
>http://msdn2.microsoft.com/en-us/library/ms998577.aspx
>The document was written in 2004. Are the arguments valid for both sql
>2000 and 2995?
No, I think not.
On SQL2005 (!), I've used openxml to process two megabyte files on a
tiny, overloaded laptop, I run five or ten nodes queries to shred it
in two seconds, and that's pretty darned good performance in my book!
Maybe 20mb would be a problem, or 200mb. Assume it does keep
everything in RAM, but tokenized. Also even small systems today do
have gigabytes of RAM, which may be wasn't so true even four years
ago.
J.
|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:knbrn3lgsftdtev6ofsnt3sv9fosai8oh5@.4ax.com...
> On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>
> No, I think not.
> On SQL2005 (!), I've used openxml to process two megabyte files on a
> tiny, overloaded laptop, I run five or ten nodes queries to shred it
> in two seconds, and that's pretty darned good performance in my book!
> Maybe 20mb would be a problem, or 200mb. Assume it does keep
> everything in RAM, but tokenized. Also even small systems today do
> have gigabytes of RAM, which may be wasn't so true even four years
> ago.
One of the problems with the legacy methods of shredding XML using the
COM-based stored procs and OPENXML is that it automatically assigned 1/8th
of your SQL Server's total memory to the XML cache. Unfortunately this is a
limitation of the MSXML parser used by SQL Server to fulfill OPENXML
requests. According to this article you could potentially run out of memory
if you prepare too many XML documents at once without releasing some.
|||FYI, here's the SQL 2005 article detailing the 1/8th memory issue:
http://msdn2.microsoft.com/en-us/library/ms187367.aspx. This issue affects
SQL Server 2005 as well as SQL Server 2000. This means that if your SQL
Server has 2 GB assigned to it, that 2 MB XML file you're processing just
got 250 MB assigned to it until you destroy the COM object with
sp_xml_removedocument.
That's quite a hefty price to pay to process a 2 MB file.
|||"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz). On
a simple 1 MB XML document I came up with the following performance:
- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds
OPENXML appears to be slightly faster for simple XML documents, although it
reserves 1/8th of the SQL Server memory regardless of the size of the XML
document. I didn't test it with more complex documents and paths, like
documents with multiple namespaces, and several levels of nesting, etc.
When you are shredding XML documents stored in a column, the nodes() method
with a primary XML index is considerably faster than either of the other two
methods.
|||Typo, 100 KB XML document, not 1 MB.
"Mike C#" <xyz@.xyz.com> wrote in message
news:eLL1CpKUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "Marc Gravell" <marc.gravell@.gmail.com> wrote in message
> news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
> On a simple 1 MB XML document I came up with the following performance:
> - shredding an XML type variable with nodes() method 1,000 times took 77
> seconds
> - shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
> - shredding an XML type column with nodes() method and primary XML index
> 1,000 times took 42 seconds
> OPENXML appears to be slightly faster for simple XML documents, although
> it reserves 1/8th of the SQL Server memory regardless of the size of the
> XML document. I didn't test it with more complex documents and paths,
> like documents with multiple namespaces, and several levels of nesting,
> etc. When you are shredding XML documents stored in a column, the nodes()
> method with a primary XML index is considerably faster than either of the
> other two methods.
>
|||My pardon, I was using openrowset('bulk ...'), and I guess that is using the
XML datatype for shredding.
Josh
"Marc Gravell" wrote:
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
>
|||"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:4F59EC99-46F2-4F48-99CF-F453DCD947C1@.microsoft.com...
> My pardon, I was using openrowset('bulk ...'), and I guess that is using
> the
> XML datatype for shredding.
?
Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type variable
or column? I ask because casting character/binary data to XML and shredding
it to relational format are two completely different tasks.
|||On Mon, 7 Jan 2008 21:32:31 -0500, "Mike C#" <xyz@.xyz.com> wrote:
>Are you actually shredding the XML data after you load it using
>openrowset(...) or are you actually just storing it an an XML type variable
>or column? I ask because casting character/binary data to XML and shredding
>it to relational format are two completely different tasks.
Cast it to XML variable with schema to validate via XSD.
Then pass it to SP as XML with no schema, shred it there with four to
six queries mostly cross applies.
Only reason I use the unschema'd XML in the SP is so I can change the
XSD without first dropping the SP. I timed it both ways, seems to
make very little difference. The shredding is indecently fast. Takes
two seconds to shred into table vars, about ten seconds to write rows
to database.
openrowset used like this doesn't do that 1/8 of RAM, does it?
Thanks.
J.
OPENXML Parsing Error: Multiple colons are not allow
I'm receiving the above parsing error for this document. I am trying to create an export that can be sent to a sister program and read into their database (same schemas). If I can export the entire entity (I do this using a union with nulls), I should
also be able to import it into another's database and maintain integrity. This is my attempt at the export half of it. I need to use OPENXML to be able to read the document once I receive it. This one is in non-binary format. The one I intend to use i
s binary, but binary is hard to debug so it is in this format. I have no idea what I am doing.
Thanks for any help.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
--Sample XML document
SET @.doc ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="Tag" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID" rs:autoincrement="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="SLSMNID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="CusID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
</s:AttributeType>
<s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c15" rs:name="P!2!active" rs:number="16" rs:nullable="true">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, "/>
<z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True"/>
<z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True" c16="394" c17="2759" c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
</rs:data>
</xml>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( QuoteID int '.',
QuoteID int '../@. QuoteID',
QuoteID int '../../@. QuoteID')
EXEC sp_xml_removedocument @.idoc
Regards,
Jamie
It is not multiple colons that are the problem but that your WITH clause
uses the same name for all the columns.
Try:
SELECT c1 as QuoteID, c2 as QuoteID, c3 as QuoteID
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( c1 int '.',
c2 int '../@.QuoteID',
c3 int '../../@.QuoteID')
Furthermore, you want your @.doc variable to be at least of size 4000. And
you probably want to use path expressions in the OpenXML that actually
extracts information from your XML.
I don't understand your comment about binary format. OpenXML requires XML
and not some binary format.
I hope now have a better idea of what you are doing :-)
HTH
Michael
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:4FFA87D1-077F-40AF-AE84-F2C1AA325BEE@.microsoft.com...
> I'm receiving the above parsing error for this document. I am trying to
> create an export that can be sent to a sister program and read into their
> database (same schemas). If I can export the entire entity (I do this
> using a union with nulls), I should also be able to import it into
> another's database and maintain integrity. This is my attempt at the
> export half of it. I need to use OPENXML to be able to read the document
> once I receive it. This one is in non-binary format. The one I intend to
> use is binary, but binary is hard to debug so it is in this format. I
> have no idea what I am doing.
> Thanks for any help.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> --Sample XML document
> SET @.doc ='
> <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
> <s:Schema id="RowsetSchema">
> <s:ElementType name="row" content="eltOnly" rs:updatable="true">
> <s:AttributeType name="Tag" rs:number="1">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3"
> rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID"
> rs:autoincrement="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
> <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="SLSMNID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="CusID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
> </s:AttributeType>
> <s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
> </s:AttributeType>
> <s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15"
> rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c15" rs:name="P!2!active" rs:number="16"
> rs:nullable="true">
> <s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:extends type="rs:rowbase"/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286"
> c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1
> electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity
> bin, "/>
> <z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True"/>
> <z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True" c16="394" c17="2759"
> c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
> </rs:data>
> </xml>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/xml/QUOTEID')
> WITH ( QuoteID int '.',
> QuoteID int '../@. QuoteID',
> QuoteID int '../../@. QuoteID')
> EXEC sp_xml_removedocument @.idoc
> --
> Regards,
> Jamie
also be able to import it into another's database and maintain integrity. This is my attempt at the export half of it. I need to use OPENXML to be able to read the document once I receive it. This one is in non-binary format. The one I intend to use i
s binary, but binary is hard to debug so it is in this format. I have no idea what I am doing.
Thanks for any help.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
--Sample XML document
SET @.doc ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="Tag" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID" rs:autoincrement="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
<s:datatype dt:type="float" dt:maxLength="8" rs:precision="15" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="SLSMNID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="CusID">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
</s:AttributeType>
<s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
</s:AttributeType>
<s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14" rs:nullable="true">
<s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15" rs:nullable="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c15" rs:name="P!2!active" rs:number="16" rs:nullable="true">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18" rs:nullable="true">
<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19" rs:nullable="true">
<s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19" rs:fixedlength="true"/>
</s:AttributeType>
<s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
</s:AttributeType>
<s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22" rs:nullable="true">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, "/>
<z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True"/>
<z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity bin, " c12="2004-07-07" c13="33" c
15="True" c16="394" c17="2759" c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
</rs:data>
</xml>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( QuoteID int '.',
QuoteID int '../@. QuoteID',
QuoteID int '../../@. QuoteID')
EXEC sp_xml_removedocument @.idoc
Regards,
Jamie
It is not multiple colons that are the problem but that your WITH clause
uses the same name for all the columns.
Try:
SELECT c1 as QuoteID, c2 as QuoteID, c3 as QuoteID
FROM OPENXML (@.idoc, '/xml/QUOTEID')
WITH ( c1 int '.',
c2 int '../@.QuoteID',
c3 int '../../@.QuoteID')
Furthermore, you want your @.doc variable to be at least of size 4000. And
you probably want to use path expressions in the OpenXML that actually
extracts information from your XML.
I don't understand your comment about binary format. OpenXML requires XML
and not some binary format.
I hope now have a better idea of what you are doing :-)
HTH
Michael
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:4FFA87D1-077F-40AF-AE84-F2C1AA325BEE@.microsoft.com...
> I'm receiving the above parsing error for this document. I am trying to
> create an export that can be sent to a sister program and read into their
> database (same schemas). If I can export the entire entity (I do this
> using a union with nulls), I should also be able to import it into
> another's database and maintain integrity. This is my attempt at the
> export half of it. I need to use OPENXML to be able to read the document
> once I receive it. This one is in non-binary format. The one I intend to
> use is binary, but binary is hard to debug so it is in this format. I
> have no idea what I am doing.
> Thanks for any help.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> --Sample XML document
> SET @.doc ='
> <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
> <s:Schema id="RowsetSchema">
> <s:ElementType name="row" content="eltOnly" rs:updatable="true">
> <s:AttributeType name="Tag" rs:number="1">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="Parent" rs:number="2" rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c2" rs:name="Q!1!QUOTEID" rs:number="3"
> rs:basecatalog="Quotes" rs:basetable="QUOTE" rs:basecolumn="QuoteID"
> rs:autoincrement="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c3" rs:name="Q!1!ExpirationDate" rs:number="4"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c4" rs:name="Q!1!DiscountPercent" rs:number="5"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="DiscountPercent">
> <s:datatype dt:type="float" dt:maxLength="8" rs:precision="15"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c5" rs:name="Q!1!slsmnid" rs:number="6"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="SLSMNID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c6" rs:name="Q!1!CUSID" rs:number="7"
> rs:writeunknown="true" rs:basecatalog="Quotes" rs:basetable="QUOTE"
> rs:basecolumn="CusID">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true" rs:maybenull="false"/>
> </s:AttributeType>
> <s:AttributeType name="c7" rs:name="Q!1!QuoteNote" rs:number="8"
> rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Quotes"
> rs:basetable="QUOTE" rs:basecolumn="QuoteNote">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="1000"/>
> </s:AttributeType>
> <s:AttributeType name="c8" rs:name="P!2!Qty" rs:number="9"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c9" rs:name="P!2!Catnum" rs:number="10"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:AttributeType name="c10" rs:name="P!2!CatDesc" rs:number="11"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
> </s:AttributeType>
> <s:AttributeType name="c11" rs:name="P!2!ExtPrice" rs:number="12"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="4" rs:precision="16" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c12" rs:name="P!2!DateVoid" rs:number="13"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10"/>
> </s:AttributeType>
> <s:AttributeType name="c13" rs:name="P!2!FrameID" rs:number="14"
> rs:nullable="true">
> <s:datatype dt:type="i2" dt:maxLength="2" rs:precision="5"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c14" rs:name="P!2!LineItemDiscount" rs:number="15"
> rs:nullable="true">
> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c15" rs:name="P!2!active" rs:number="16"
> rs:nullable="true">
> <s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c16" rs:name="B!3!QUOTEDPARTID" rs:number="17"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c17" rs:name="B!3!MACOLAID" rs:number="18"
> rs:nullable="true">
> <s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19"
> rs:scale="0" rs:precision="9" rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c18" rs:name="B!3!QUOTEBATCHID" rs:number="19"
> rs:nullable="true">
> <s:datatype dt:type="i8" dt:maxLength="8" rs:precision="19"
> rs:fixedlength="true"/>
> </s:AttributeType>
> <s:AttributeType name="c19" rs:name="B!3!TIMEENTERED" rs:number="20"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c20" rs:name="B!3!TIMEPRINTED" rs:number="21"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5"/>
> </s:AttributeType>
> <s:AttributeType name="c21" rs:name="B!3!QUOTENUMPRINTED" rs:number="22"
> rs:nullable="true">
> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="50"/>
> </s:AttributeType>
> <s:extends type="rs:rowbase"/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row Tag="1" c2="176" c3="2004-07-03" c4="0" c5="45" c6="1286"
> c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz, Phase 1
> electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton capacity
> bin, "/>
> <z:row Tag="2" Parent="1" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True"/>
> <z:row Tag="3" Parent="2" c2="176" c3="2004-07-03" c4="0" c5="45"
> c6="1286" c7="500x40 ft. Broiler House w/ 25000 birds, 220 volt, 60 Hz,
> Phase 1 electric, 2 feed lines, w/winching, 65 birds/pan, w/ 20.92 ton
> capacity bin, " c12="2004-07-07" c13="33" c15="True" c16="394" c17="2759"
> c18="105978" c19="17.6" c20="17.6" c21="JRL0004500176"/>
> </rs:data>
> </xml>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/xml/QUOTEID')
> WITH ( QuoteID int '.',
> QuoteID int '../@. QuoteID',
> QuoteID int '../../@. QuoteID')
> EXEC sp_xml_removedocument @.idoc
> --
> Regards,
> Jamie
Subscribe to:
Posts (Atom)