Help needed: Granting Create table permisions on specific Schema Options

Having some trouble getting my head around setting access to specific
schemas- here's my problem:

I've created a specific schema that I only want certain users to
control

Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):

GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'gdev (paul.afamdi.okeke@.gmail.com) writes:

Quote:

Originally Posted by

Having some trouble getting my head around setting access to specific
schemas- here's my problem:
>
I've created a specific schema that I only want certain users to
control
>
>
Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):
>
GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

You also need:

GRANT CREATE TABLE TO eflyerAdmin

And it's sufficient to grant CONTROL on the schema, since CONTROL implies
the rest.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help needed with importing XML

SQL server 2005
ive been trying very unsucessfully to try and import an xml file into a
SQL2005 table. I have a schema but am unsure as what to do next. I have bee
n
looking at lots od different exmples but its way over my head. i need a ste
p
by step example that will import the following xml file into two tables
<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
table 1 will contain all the header information in fields , reportType,
advicenumber etc as all the address info into table 2.
Once i have created the tables, is there a way to just do the import direct
in TSQL
any help will be very welcome as im struggling to grasp thisHi Peter,
I've made up an really simple table definition for table1 and table2 and
left some of the columns out to cut down on size. You want to decompose the
XML into relational name-value pairs and do an insert of pieces into two
separate tables. This is *assuming* you don't have more than one Header or
AddresseeInformation in the document, or more than one document in the XML.
Example follows mail message.
First way is the easiest. Use the xml.value method to extract each value,
given the attribute name in the document, 1 column per column in the rowset
(table) you want. This may be able to be optimized by changing the query,
but I'm trying to keep it simple for exposition.
Second way is to use xml.nodes method to obtain a rowset of name-value
pairs. Then use the PIVOT operator to pivot the values into one row and
multiple column values. I've done it in two steps (#temp table) to
illustrate what nodes returns, then combined it into one step.
You could also use OpenXML to do this, but it might require more storage
overhead.
Hope this helps,
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
CREATE TABLE table1 (
reporttype varchar(100),
adviceNumber varchar(100),
currentProcessingDate datetime
)
go
CREATE TABLE table2 (
name varchar(100),
address1 varchar(100),
address2 varchar(100)
)
go
declare @.x xml
set @.x =
'<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
'
/* First way, using xml.value
INSERT table1
select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDate',
'datetime') as c
SELECT * FROM table1
-- now do the same for table2
INSERT table2
select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address1',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address2',
'varchar(100)') as c
SELECT * FROM table2
*/
/* Second way, using xml.nodes, intermediate table for exposition
select t.c.value('local-name(.)', 'varchar(50)') as Name,
t.c.value('data(.)', 'varchar(100)') as Value
into #temp
from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
select * from #temp
SELECT [reportType], [adviceNumber], [currentProcessingDate]
FROM #temp
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
*/
-- third way, combination of second way into one statement.
insert table1
select [reportType], [adviceNumber], [currentProcessingDate]
from
(
SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
t.c.value('data(.)', 'varchar(100)') as [Value]
FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
) AS namevalue
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
> SQL server 2005
> ive been trying very unsucessfully to try and import an xml file into a
> SQL2005 table. I have a schema but am unsure as what to do next. I have
> been
> looking at lots od different exmples but its way over my head. i need a
> step
> by step example that will import the following xml file into two tables
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place
> "
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> table 1 will contain all the header information in fields , reportType,
> advicenumber etc as all the address info into table 2.
> Once i have created the tables, is there a way to just do the import
> direct
> in TSQL
> any help will be very welcome as im struggling to grasp this|||Bob,
Thank you very much, that has showed me a lot and i have managed to adapt it
to the nomal xml files that im currently recieving, however, and theres
always a however, some of the files have more than one element of the ssame
name, so in the file i have shown earlier, how would i handle it if say it
had three AddresseeInformation for example
thansk in advance
"Bob Beauchemin" wrote:

> Hi Peter,
> I've made up an really simple table definition for table1 and table2 and
> left some of the columns out to cut down on size. You want to decompose th
e
> XML into relational name-value pairs and do an insert of pieces into two
> separate tables. This is *assuming* you don't have more than one Header or
> AddresseeInformation in the document, or more than one document in the XML
.
> Example follows mail message.
> First way is the easiest. Use the xml.value method to extract each value,
> given the attribute name in the document, 1 column per column in the rowse
t
> (table) you want. This may be able to be optimized by changing the query,
> but I'm trying to keep it simple for exposition.
> Second way is to use xml.nodes method to obtain a rowset of name-value
> pairs. Then use the PIVOT operator to pivot the values into one row and
> multiple column values. I've done it in two steps (#temp table) to
> illustrate what nodes returns, then combined it into one step.
> You could also use OpenXML to do this, but it might require more storage
> overhead.
> Hope this helps,
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> CREATE TABLE table1 (
> reporttype varchar(100),
> adviceNumber varchar(100),
> currentProcessingDate datetime
> )
> go
> CREATE TABLE table2 (
> name varchar(100),
> address1 varchar(100),
> address2 varchar(100)
> )
> go
> declare @.x xml
> set @.x =
> '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place
"
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> '
> /* First way, using xml.value
> INSERT table1
> select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDa
te',
> 'datetime') as c
> SELECT * FROM table1
> -- now do the same for table2
> INSERT table2
> select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name'
,
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.addre
ss1',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.addre
ss2',
> 'varchar(100)') as c
> SELECT * FROM table2
> */
>
> /* Second way, using xml.nodes, intermediate table for exposition
> select t.c.value('local-name(.)', 'varchar(50)') as Name,
> t.c.value('data(.)', 'varchar(100)') as Value
> into #temp
> from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> select * from #temp
> SELECT [reportType], [adviceNumber], [currentProcessingDate]
> FROM #temp
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> */
> -- third way, combination of second way into one statement.
> insert table1
> select [reportType], [adviceNumber], [currentProcessingDate]
> from
> (
> SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
> t.c.value('data(.)', 'varchar(100)') as [Value]
> FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> ) AS namevalue
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
>
>|||Hi Peter,
Those examples showed how to decompose arbitrary XML in multiple unrealated
tables. In a relational database you need to have something tying together
table1 and table2 (adviseNumber?, reportType?). There's nothing in the
document to deduce this. Also, relational doesn't allow repeating groups, so
you'd need a discriminator to distinguish between the 3 nodes. You could
either use ordinal (as I used [1] in the first example to indicate the 1st
AddresseeInformation) or use nodes to do it in one step and insert multiple
rows. But there has to be something in the relational schema tying table1
and table2 together.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...
> Bob,
> Thank you very much, that has showed me a lot and i have managed to adapt
> it
> to the nomal xml files that im currently recieving, however, and theres
> always a however, some of the files have more than one element of the
> ssame
> name, so in the file i have shown earlier, how would i handle it if say
> it
> had three AddresseeInformation for example
> thansk in advance
> "Bob Beauchemin" wrote:
>|||Bob,
Ive spent he entire day chasing ghosts tying to assing a discriminator , its
easy to code when i know how meny there will be but as these reports are
dynamic i need to find a way to change rowcount to equal the ordinal, and
loop till all the rows have been imported
SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
INTO #ReturnedItem
FROM
@.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/Ori
ginatingAccountRecord/ReturnedCreditItem[1]/@.*') as t(c)
youve been such a great help so far, think one i can resolve this issue i
can carry on on my own
"Bob Beauchemin" wrote:

> Hi Peter,
> Those examples showed how to decompose arbitrary XML in multiple unrealate
d
> tables. In a relational database you need to have something tying together
> table1 and table2 (adviseNumber?, reportType?). There's nothing in the
> document to deduce this. Also, relational doesn't allow repeating groups,
so
> you'd need a discriminator to distinguish between the 3 nodes. You could
> either use ordinal (as I used [1] in the first example to indicate the 1st
> AddresseeInformation) or use nodes to do it in one step and insert multipl
e
> rows. But there has to be something in the relational schema tying table1
> and table2 together.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...
>
>|||If I think I'm understanding what you're asking, there's a few ways to do
this. You could add a gratuitous identity column to #temp and use
INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
variable until the nodes function returns no nodes, using sql:variable in
the XQuery predicate. You could have also changed the XPath expression to a
FLWOR expression and selected the position, but SQL Server XQuery doesn't
support the "at" portion of "for $x at $y in ..." syntax or the position()
function used outside of the predicate.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...
> Bob,
> Ive spent he entire day chasing ghosts tying to assing a discriminator ,
> its
> easy to code when i know how meny there will be but as these reports are
> dynamic i need to find a way to change rowcount to equal the ordinal, and
> loop till all the rows have been imported
> SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
> t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
> INTO #ReturnedItem
> FROM
> @.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/O
riginatingAccountRecord/ReturnedCreditItem[1]/@.*')
> as t(c)
>
> youve been such a great help so far, think one i can resolve this issue i
> can carry on on my own
> "Bob Beauchemin" wrote:
>|||Bob,
Thanks again for the pointers yhoi have to admit ive spend a few hours and
srtill carnt grasp it. I understand what you were saying about the
relationship between table 1 and table 2, that i think i can work out, what
im still struggling with is returning the pivot table for the three addresss
( only for example ). I still can not get the identity colum in using your
previous eamaples. sorry to be a pain but can you pint me in the direction
of an example based of what you have already explained
thanks again for all your help
"Bob Beauchemin" wrote:

> If I think I'm understanding what you're asking, there's a few ways to do
> this. You could add a gratuitous identity column to #temp and use
> INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
> variable until the nodes function returns no nodes, using sql:variable in
> the XQuery predicate. You could have also changed the XPath expression to
a
> FLWOR expression and selected the position, but SQL Server XQuery doesn't
> support the "at" portion of "for $x at $y in ..." syntax or the position(
)
> function used outside of the predicate.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...
>
>

Help needed with importing XML

SQL server 2005
ive been trying very unsucessfully to try and import an xml file into a
SQL2005 table. I have a schema but am unsure as what to do next. I have been
looking at lots od different exmples but its way over my head. i need a step
by step example that will import the following xml file into two tables
<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
table 1 will contain all the header information in fields , reportType,
advicenumber etc as all the address info into table 2.
Once i have created the tables, is there a way to just do the import direct
in TSQL
any help will be very welcome as im struggling to grasp this
Hi Peter,
I've made up an really simple table definition for table1 and table2 and
left some of the columns out to cut down on size. You want to decompose the
XML into relational name-value pairs and do an insert of pieces into two
separate tables. This is *assuming* you don't have more than one Header or
AddresseeInformation in the document, or more than one document in the XML.
Example follows mail message.
First way is the easiest. Use the xml.value method to extract each value,
given the attribute name in the document, 1 column per column in the rowset
(table) you want. This may be able to be optimized by changing the query,
but I'm trying to keep it simple for exposition.
Second way is to use xml.nodes method to obtain a rowset of name-value
pairs. Then use the PIVOT operator to pivot the values into one row and
multiple column values. I've done it in two steps (#temp table) to
illustrate what nodes returns, then combined it into one step.
You could also use OpenXML to do this, but it might require more storage
overhead.
Hope this helps,
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
CREATE TABLE table1 (
reporttype varchar(100),
adviceNumber varchar(100),
currentProcessingDate datetime
)
go
CREATE TABLE table2 (
name varchar(100),
address1 varchar(100),
address2 varchar(100)
)
go
declare @.x xml
set @.x =
'<?xml version="1.0" encoding="ISO-8859-1"?>
<BACSDocument>
<Data>
<ARUCS>
<Header reportType="REFT1027" adviceNumber="01077"
currentProcessingDate="2005-12-05"></Header>
<AddresseeInformation name="Mr Bean "
address1="Company Name " address2="This Place "
address3="This Town " address4="This County
" address5="A12 45T "></AddresseeInformation>
</ARUCS>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
'
/* First way, using xml.value
INSERT table1
select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDate',
'datetime') as c
SELECT * FROM table1
-- now do the same for table2
INSERT table2
select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name',
'varchar(100)') as a,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address1',
'varchar(100)') as b,
@.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address2',
'varchar(100)') as c
SELECT * FROM table2
*/
/* Second way, using xml.nodes, intermediate table for exposition
select t.c.value('local-name(.)', 'varchar(50)') as Name,
t.c.value('data(.)', 'varchar(100)') as Value
into #temp
from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
select * from #temp
SELECT [reportType], [adviceNumber], [currentProcessingDate]
FROM #temp
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
*/
-- third way, combination of second way into one statement.
insert table1
select [reportType], [adviceNumber], [currentProcessingDate]
from
(
SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
t.c.value('data(.)', 'varchar(100)') as [Value]
FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
) AS namevalue
PIVOT (
MAX([Value]) FOR
[Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
) as p
-- now do the same for table2 (elided)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
> SQL server 2005
> ive been trying very unsucessfully to try and import an xml file into a
> SQL2005 table. I have a schema but am unsure as what to do next. I have
> been
> looking at lots od different exmples but its way over my head. i need a
> step
> by step example that will import the following xml file into two tables
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place
> "
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> table 1 will contain all the header information in fields , reportType,
> advicenumber etc as all the address info into table 2.
> Once i have created the tables, is there a way to just do the import
> direct
> in TSQL
> any help will be very welcome as im struggling to grasp this
|||Bob,
Thank you very much, that has showed me a lot and i have managed to adapt it
to the nomal xml files that im currently recieving, however, and theres
always a however, some of the files have more than one element of the ssame
name, so in the file i have shown earlier, how would i handle it if say it
had three AddresseeInformation for example
thansk in advance
"Bob Beauchemin" wrote:

> Hi Peter,
> I've made up an really simple table definition for table1 and table2 and
> left some of the columns out to cut down on size. You want to decompose the
> XML into relational name-value pairs and do an insert of pieces into two
> separate tables. This is *assuming* you don't have more than one Header or
> AddresseeInformation in the document, or more than one document in the XML.
> Example follows mail message.
> First way is the easiest. Use the xml.value method to extract each value,
> given the attribute name in the document, 1 column per column in the rowset
> (table) you want. This may be able to be optimized by changing the query,
> but I'm trying to keep it simple for exposition.
> Second way is to use xml.nodes method to obtain a rowset of name-value
> pairs. Then use the PIVOT operator to pivot the values into one row and
> multiple column values. I've done it in two steps (#temp table) to
> illustrate what nodes returns, then combined it into one step.
> You could also use OpenXML to do this, but it might require more storage
> overhead.
> Hope this helps,
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> CREATE TABLE table1 (
> reporttype varchar(100),
> adviceNumber varchar(100),
> currentProcessingDate datetime
> )
> go
> CREATE TABLE table2 (
> name varchar(100),
> address1 varchar(100),
> address2 varchar(100)
> )
> go
> declare @.x xml
> set @.x =
> '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BACSDocument>
> <Data>
> <ARUCS>
> <Header reportType="REFT1027" adviceNumber="01077"
> currentProcessingDate="2005-12-05"></Header>
> <AddresseeInformation name="Mr Bean "
> address1="Company Name " address2="This Place "
> address3="This Town " address4="This County
> " address5="A12 45T "></AddresseeInformation>
> </ARUCS>
> </Data>
> <SignatureMethod></SignatureMethod>
> <Signature></Signature>
> </BACSDocument>
> '
> /* First way, using xml.value
> INSERT table1
> select @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.reportType',
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.adviceNumber',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/Header)[1]/@.currentProcessingDate',
> 'datetime') as c
> SELECT * FROM table1
> -- now do the same for table2
> INSERT table2
> select @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.name',
> 'varchar(100)') as a,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address1',
> 'varchar(100)') as b,
> @.x.value('(/BACSDocument/Data/ARUCS/AddresseeInformation)[1]/@.address2',
> 'varchar(100)') as c
> SELECT * FROM table2
> */
>
> /* Second way, using xml.nodes, intermediate table for exposition
> select t.c.value('local-name(.)', 'varchar(50)') as Name,
> t.c.value('data(.)', 'varchar(100)') as Value
> into #temp
> from @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> select * from #temp
> SELECT [reportType], [adviceNumber], [currentProcessingDate]
> FROM #temp
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> */
> -- third way, combination of second way into one statement.
> insert table1
> select [reportType], [adviceNumber], [currentProcessingDate]
> from
> (
> SELECT t.c.value('local-name(.)', 'varchar(50)') as [Name],
> t.c.value('data(.)', 'varchar(100)') as [Value]
> FROM @.x.nodes('/BACSDocument/Data/ARUCS/Header/@.*') as t(c)
> ) AS namevalue
> PIVOT (
> MAX([Value]) FOR
> [Name] IN ([reportType], [adviceNumber], [currentProcessingDate])
> ) as p
> -- now do the same for table2 (elided)
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:E8722036-4DD5-4F15-A4FB-6CF9BF9044EF@.microsoft.com...
>
>
|||Hi Peter,
Those examples showed how to decompose arbitrary XML in multiple unrealated
tables. In a relational database you need to have something tying together
table1 and table2 (adviseNumber?, reportType?). There's nothing in the
document to deduce this. Also, relational doesn't allow repeating groups, so
you'd need a discriminator to distinguish between the 3 nodes. You could
either use ordinal (as I used [1] in the first example to indicate the 1st
AddresseeInformation) or use nodes to do it in one step and insert multiple
rows. But there has to be something in the relational schema tying table1
and table2 together.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...[vbcol=seagreen]
> Bob,
> Thank you very much, that has showed me a lot and i have managed to adapt
> it
> to the nomal xml files that im currently recieving, however, and theres
> always a however, some of the files have more than one element of the
> ssame
> name, so in the file i have shown earlier, how would i handle it if say
> it
> had three AddresseeInformation for example
> thansk in advance
> "Bob Beauchemin" wrote:
|||Bob,
Ive spent he entire day chasing ghosts tying to assing a discriminator , its
easy to code when i know how meny there will be but as these reports are
dynamic i need to find a way to change rowcount to equal the ordinal, and
loop till all the rows have been imported
SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
INTO #ReturnedItem
FROM
@.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedCreditItem[1]/@.*') as t(c)
youve been such a great help so far, think one i can resolve this issue i
can carry on on my own
"Bob Beauchemin" wrote:

> Hi Peter,
> Those examples showed how to decompose arbitrary XML in multiple unrealated
> tables. In a relational database you need to have something tying together
> table1 and table2 (adviseNumber?, reportType?). There's nothing in the
> document to deduce this. Also, relational doesn't allow repeating groups, so
> you'd need a discriminator to distinguish between the 3 nodes. You could
> either use ordinal (as I used [1] in the first example to indicate the 1st
> AddresseeInformation) or use nodes to do it in one step and insert multiple
> rows. But there has to be something in the relational schema tying table1
> and table2 together.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:3D443871-A973-4407-96EE-5E44EA309BBF@.microsoft.com...
>
>
|||If I think I'm understanding what you're asking, there's a few ways to do
this. You could add a gratuitous identity column to #temp and use
INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
variable until the nodes function returns no nodes, using sql:variable in
the XQuery predicate. You could have also changed the XPath expression to a
FLWOR expression and selected the position, but SQL Server XQuery doesn't
support the "at" portion of "for $x at $y in ..." syntax or the position()
function used outside of the predicate.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...[vbcol=seagreen]
> Bob,
> Ive spent he entire day chasing ghosts tying to assing a discriminator ,
> its
> easy to code when i know how meny there will be but as these reports are
> dynamic i need to find a way to change rowcount to equal the ordinal, and
> loop till all the rows have been imported
> SELECT t.c.value('local-name(.)', 'varchar(50)') AS Name,
> t.c.value('data(.)', 'Varchar(100)') as Value, '1' as RowNumber
> INTO #ReturnedItem
> FROM
> @.XMLDOC.nodes('/BACSDocument/Data/ARUCS/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedCreditItem[1]/@.*')
> as t(c)
>
> youve been such a great help so far, think one i can resolve this issue i
> can carry on on my own
> "Bob Beauchemin" wrote:
|||Bob,
Thanks again for the pointers yhoi have to admit ive spend a few hours and
srtill carnt grasp it. I understand what you were saying about the
relationship between table 1 and table 2, that i think i can work out, what
im still struggling with is returning the pivot table for the three addresss
( only for example ). I still can not get the identity colum in using your
previous eamaples. sorry to be a pain but can you pint me in the direction
of an example based of what you have already explained
thanks again for all your help
"Bob Beauchemin" wrote:

> If I think I'm understanding what you're asking, there's a few ways to do
> this. You could add a gratuitous identity column to #temp and use
> INSERT...SELECT instead of SELECT INTO. You could loop using a T-SQL
> variable until the nodes function returns no nodes, using sql:variable in
> the XQuery predicate. You could have also changed the XPath expression to a
> FLWOR expression and selected the position, but SQL Server XQuery doesn't
> support the "at" portion of "for $x at $y in ..." syntax or the position()
> function used outside of the predicate.
> Hope this helps,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:F327D2A5-07D4-4F37-82BA-23B4BEA60226@.microsoft.com...
>
>

Help me with this query

Hi All,

I have to table, Order and OrderPayment, and the schema looks like this:

Order
----
OrderID,
InvoiceDT,
InvoiceTotal

OrderPayment
--------
PaymentID
OrderID
PaymentAmount
PaymentDT

And I want to get all the order with Invoice DT Between certain date. Here is my query:

SELECT

o.OrderID,

o.InvoiceDT, o.InvoiceTotal,

op.PaymentAmount

FROM

[Order] o

LEFTJOIN[OrderPayment] opON op.OrderID= o.OrderID

WHERE

o.InvoiceDTISNOTNULLAND

(o.InvoiceDTBETWEEN'09/01/2006'AND'09/20/2006')

But I get two results with the same orderID, eventhough I use GROUP BY

Any idea...

Try something like this

SELECT o.OrderID,o.InvoiceDT, o.InvoiceTotal,op.PaymentAmount

FROM [Order] o
LEFT JOIN (Select orderID, sum(paymentamount) PaymentAmount from [OrderPayment] group by orderid) op ON op.OrderID = o.OrderID
WHERE o.InvoiceDT IS NOT NULL AND (o.InvoiceDT BETWEEN '09/01/2006' AND '09/20/2006')
group by o.OrderID,o.InvoiceDT,o.InvoiceTotal

|||Thanks, it works...

Help me with DB schema comparison method

Hi!
can some one help me guide to knwo as to how do we compare teh database schemas of two databases.. i mean in case i have to compare the schema of 2 ole dbs or 2 sql databases then i can do it using teh XML schema \ and compare but then in case i need to compare a SQL schema and a oledb schema then how do i go about it.. since the long datatype in ms access ( using ole db) is text in SQL and tiny int is int16... there are some other discripancies liek that. So how do i go about checking teh database schemas for such disperate DBs. Can soem one tell me some site or give soem guidance in this regard. Please help me as i need it for my project where i need to compare teh schema and alert when they are not compatible.. Please help...thanks in advance..

regards,
GautamDo you have microsoft visio available to you? It can reverse engineer your database to output a great map for you to review.|||hey crispy!!!
That did not help me in anyway...please suggest somethign good... by the way .. i am sorry i did not use visio tool before.

Thanks anyway
Gautam

help me with a bit of a practice issue...

Ok.

I recently started developing a web site for a client using storefront.net and ms sql server.

the db schema of storefront.net has autonumbers as the PKs for the products table (even though the products table contains an additional field for product_number.)

So here's my dilemma if you care to read:

I typically develop local, deploy remote (after testing). I have a local SQL server, and then the remote SQL server.

When I'm developing for this project, I'll insert data such as products to the products table (sometimes several times while i'm working out routines to import data to the products table.) this has the effect of creating a unique ID for each product based upon SQL auto-incrementing INTs.

This StoreFront.net (SF.NET) has another table that is a lookup table. For each part number, it has a corresponding categoryID number.

Now, if i have product_ID 1234, and I set the category ID to say 10 and get it working on my local box, every thing is fine.

Here's where the problem comes in: When I use DTS to transfer the database during remote deployment, each product is inserted into the remote DBs products table and gets a NEW product id. Same with the categories.

This has the effect of breaking the relationships. (SF.NET has no ref integrity nor relationships defined in the db.) let's say my product_id 1234 gets put into the remote copy, it'll get a new product_ID (PK). let's say it's now 5775. now my category ID will also get a new value. so my data is now not related.

I don't know how to handle this situation. The unique IDs generated on my local sql will nearly almost always be different from those generated on the remote db.

How do i handle this situatoin is my question? advice, guys?why are you using the product_id in the relation...shouldn't you be relating the product_number to the categoryId... rather ? which will stay fixed... ?|||well, not to be a smart alec, but duh. that's what should be done.

problem is the team who wrote this SF.NET app don't do that. they use the productID autonumber and categoryID autonumber.

so for example, when you use their merchant tools to set a product into a category, behind the scenes they are using the autonumber id of the product and not the REAL product number. same with category ID.|||I personally prefer the way StoreFront has implemented a surrogate key. This is somewhat of a religious topic in that proponents of each method are pretty adamant that "their" way is right :-)

Anyway, with DTS there is an option to "Enable identity insert" on the Options tab. Turning this on should cure your problem.

Terri