Refering to a parent element with openxml

I have this XML structure:
<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>
I can successfully use openxml to select all the supply records and insert
them in to a table using
insert into
Supply
select
Total
from
OPENXML
( @.iDoc, @.XPath, 1 )
with (
Total smallint,
)
But how can I reference the Number attribute in the parent Branch element as
follows:
insert into
Supply
select
BranchNumber
Total
from
...
Thanks!
Here's one way:
declare @.xml varchar(2000)
set @.xml = '<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>'
declare @.hdoc int
exec sp_xml_preparedocument @.hdoc output, @.xml
select *
from openxml(@.hdoc, 'Branch/Supply', 1)
with (
total smallint '@.Total',
branch smallint '../@.Number'
)
exec sp_xml_removedocument @.hdoc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Xerox" <anon@.anon.com> wrote in message
news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have this XML structure:
> <Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>
> I can successfully use openxml to select all the supply records and insert
> them in to a table using
> insert into
> Supply
> select
> Total
> from
> OPENXML
> ( @.iDoc, @.XPath, 1 )
> with (
> Total smallint,
> )
> But how can I reference the Number attribute in the parent Branch element
as
> follows:
> insert into
> Supply
> select
> BranchNumber
> Total
> from
> ...
> Thanks!
>
|||Thats great! Thanks Adam
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uT#6je$IFHA.2604@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here's one way:
>
> declare @.xml varchar(2000)
> set @.xml = '<Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>'
> declare @.hdoc int
> exec sp_xml_preparedocument @.hdoc output, @.xml
> select *
> from openxml(@.hdoc, 'Branch/Supply', 1)
> with (
> total smallint '@.Total',
> branch smallint '../@.Number'
> )
> exec sp_xml_removedocument @.hdoc
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Xerox" <anon@.anon.com> wrote in message
> news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
insert[vbcol=seagreen]
element
> as
>

Refering to a parent element with openxml

I have this XML structure:
<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>
I can successfully use openxml to select all the supply records and insert
them in to a table using
insert into
Supply
select
Total
from
OPENXML
( @.iDoc, @.XPath, 1 )
with (
Total smallint,
)
But how can I reference the Number attribute in the parent Branch element as
follows:
insert into
Supply
select
BranchNumber
Total
from
...
Thanks!Here's one way:
declare @.xml varchar(2000)
set @.xml = '<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>'
declare @.hdoc int
exec sp_xml_preparedocument @.hdoc output, @.xml
select *
from openxml(@.hdoc, 'Branch/Supply', 1)
with (
total smallint '@.Total',
branch smallint '../@.Number'
)
exec sp_xml_removedocument @.hdoc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Xerox" <anon@.anon.com> wrote in message
news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have this XML structure:
> <Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>
> I can successfully use openxml to select all the supply records and insert
> them in to a table using
> insert into
> Supply
> select
> Total
> from
> OPENXML
> ( @.iDoc, @.XPath, 1 )
> with (
> Total smallint,
> )
> But how can I reference the Number attribute in the parent Branch element
as
> follows:
> insert into
> Supply
> select
> BranchNumber
> Total
> from
> ...
> Thanks!
>|||Thats great! Thanks Adam
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uT#6je$IFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Here's one way:
>
> declare @.xml varchar(2000)
> set @.xml = '<Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>'
> declare @.hdoc int
> exec sp_xml_preparedocument @.hdoc output, @.xml
> select *
> from openxml(@.hdoc, 'Branch/Supply', 1)
> with (
> total smallint '@.Total',
> branch smallint '../@.Number'
> )
> exec sp_xml_removedocument @.hdoc
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Xerox" <anon@.anon.com> wrote in message
> news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
insert
element
> as
>

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Referencing query results by number

Is there a way to reference the results of a query by column number?
i.e. select Name, City from tblEmployee
so referencing field 1 from the query in a table will list all the employee
names
Thanks.select Name As Field1, City As Field2 from tblEmployee
OR
select au_id As [1], city As [2], state As [3] from authors
[In the latter case, the fields will be generated as ID1, ID2, and ID3
respectively.]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ali Jaffer" <Ali Jaffer@.discussions.microsoft.com> wrote in message
news:0BB8CBE4-0FCD-4444-B00B-99EDE49CF5D0@.microsoft.com...
> Is there a way to reference the results of a query by column number?
> i.e. select Name, City from tblEmployee
> so referencing field 1 from the query in a table will list all the
employee
> names
> Thanks.

Referencing AS columns

I'm just converting my Access database to SQL Server and have come across a number of differences. In Access I was able to do the following:
SELECT ZNew = Max( ..
ZNew2 = [ZNew] - Price
FROM ...
Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max(' line? Help would be appreciated. Thanks.Try:

SELECT ZNew = Max([ZNew] - Price)
FROM ...

blindman|||Did you mean ZNew2 = Max([ZNew] etc. ...
The first line ZNew = Max( .. is an extensive CASE evaluation.
Nice not to have to repeat it in the ZNew2 expression.|||Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max

No, seems to me that you must specify all the syntax :

SELECT ZNew = Max( ...),
ZNew2 = Max(...) - Price
FROM ...|||If you are using a case function you need to show us your statement.

blindman|||Here's the code:
SELECT TCode, ZValue = Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500 ELSE TValue END) ,
ZNew2 = ZValue - TKgValue|||You haven't included your FROM clause, so I can't tell if ZValue exists in an underlying table as well as being constructed from your case clause. If your tables have a field called ZValue in them, that is the value that will be used when you try to calculate ZNew2. Otherwise, I think you will get an error stating that SQL Server can't find field ZValue. You cannot create it and then reference it in the same statement, so you will have to repeat your case statement.

There are ways to avoid repeating the CASE statement, such as this method using nested queries:

SELECT TCode,
ZValue,
ZValue - TKgValue ZNew2
FROM (SELECT TCode,
Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500
ELSE TValue END) ZValue,
TKgValue
From YourTableReferencese) ZValueSubquery

blindman|||The Value does not exist and a nested query will not work in this case, so I'll just have to repeat the CASE statement. Thanks.|||I think your code would be easier to maintain, (and may run faster) if you use the nested query approach.

blindman

Referencing a total for a group inside that group?

Is there any way to do this' I am trying to display percentages of the total
along with the number of items. What I want to do would look something like
this.
Group header:
Detail X X/Sum of all X's
Group footer Sum of all X's
I can't seem to find a way to refer to the item in the footer inside the
group. Can this be done? If so how?
Thanks in advance.I found it!
What I had to do was:
Group header:
Detail X X/Sum(X,"Groupname")
Group footer Sum of all X's
In case anyone has the same problem.
"Doug" wrote:
> Is there any way to do this' I am trying to display percentages of the total
> along with the number of items. What I want to do would look something like
> this.
> Group header:
> Detail X X/Sum of all X's
> Group footer Sum of all X's
> I can't seem to find a way to refer to the item in the footer inside the
> group. Can this be done? If so how?
> Thanks in advance.
>

Reference previously calculated values in a table between rows

I have a requirement where I produce a number of rows in a table footer and each subsequent rows needs values calculated in the previous row. Fields in columns can be given a name and used in another column of the same row by using "=ReportItems!textbox1.Value".
Is the same thing available between rows?
Thanks for any help.You can refer to the previous value of fields (expressions) in table rows.
Try e.g.:
=Previews(Fields!abc.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"lsff" <lsff@.discussions.microsoft.com> wrote in message
news:C73A4807-BCE9-4B58-A2FC-4E894C07A30D@.microsoft.com...
> I have a requirement where I produce a number of rows in a table footer
and each subsequent rows needs values calculated in the previous row. Fields
in columns can be given a name and used in another column of the same row by
using "=ReportItems!textbox1.Value".
> Is the same thing available between rows?
> Thanks for any help.|||Sorry, I meant:
=Previous(Fields!abc.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uA75dBFdEHA.3148@.TK2MSFTNGP10.phx.gbl...
> You can refer to the previous value of fields (expressions) in table rows.
> Try e.g.:
> =Previews(Fields!abc.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "lsff" <lsff@.discussions.microsoft.com> wrote in message
> news:C73A4807-BCE9-4B58-A2FC-4E894C07A30D@.microsoft.com...
> > I have a requirement where I produce a number of rows in a table footer
> and each subsequent rows needs values calculated in the previous row.
Fields
> in columns can be given a name and used in another column of the same row
by
> using "=ReportItems!textbox1.Value".
> >
> > Is the same thing available between rows?
> >
> > Thanks for any help.
>|||Robert,
This didn't do what I needed. For example, I have a field which contains a sum of different values (call it field A). In the next row, I calculate a value based on field A and another field in a different dataset (call this field B). In the third row, I want the total of Fields A and B (call this Field C). At the moment, I include in Field C the formula for field A and add to it the formula from Field B. I have a number of other rows wioth progressive additions. So you can see the formula in the fields keep on getting bigger and bigger. It would be nice to just say Field A + Field B, etc.
"Robert Bruckner [MSFT]" wrote:
> Sorry, I meant:
> =Previous(Fields!abc.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:uA75dBFdEHA.3148@.TK2MSFTNGP10.phx.gbl...
> > You can refer to the previous value of fields (expressions) in table rows.
> > Try e.g.:
> > =Previews(Fields!abc.Value)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "lsff" <lsff@.discussions.microsoft.com> wrote in message
> > news:C73A4807-BCE9-4B58-A2FC-4E894C07A30D@.microsoft.com...
> > > I have a requirement where I produce a number of rows in a table footer
> > and each subsequent rows needs values calculated in the previous row.
> Fields
> > in columns can be given a name and used in another column of the same row
> by
> > using "=ReportItems!textbox1.Value".
> > >
> > > Is the same thing available between rows?
> > >
> > > Thanks for any help.
> >
> >
>
>

Re-engineering CREATE STATISTICS

Hello Folks,

I have a SQL Server 2000 database that has a number of named statitics on it. I unfortunately do not have the actual CREATE STATISTICS that were run against this database. I have been able to find these by using this query:

SELECT DISTINCT
object_name(i.id) as TableName
, i.Name as StatName
, c.name as ColName
FROM SysIndexes i
inner join SysIndexKeys k
on i.ID = k.id
inner join SysColumns c
on k.id = c.id and c.colid = k.colid
WHERE IndexProperty(i.id, i.name, 'IsStatistics') = 1
AND OBJECTPROPERTY (i.id, 'IsMsShipped') = 0
and i.name not like '_WA%'
order by i.name

My question is: Is there a way to reverse engineer or see the keywords that were used to create the statitics. I'm interested in this portion of the CREATE:

WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT |

ROWS } ] [ , ] ]
[ NORECOMPUTE ]

Thanks, MarkAny ideas?

Re-engineering CREATE STATISTICS

Hello Folks,

I have a SQL Server 2000 database that has a number of named statitics on it. I unfortunately do not have the actual CREATE STATISTICS that were run against this database. I have been able to find these by using this query:

SELECT DISTINCT
object_name(i.id) as TableName
, i.Name as StatName
, c.name as ColName
FROM SysIndexes i
inner join SysIndexKeys k
on i.ID = k.id
inner join SysColumns c
on k.id = c.id and c.colid = k.colid
WHERE IndexProperty(i.id, i.name, 'IsStatistics') = 1
AND OBJECTPROPERTY (i.id, 'IsMsShipped') = 0
and i.name not like '_WA%'
order by i.name

My question is: Is there a way to reverse engineer or see the keywords that were used to create the statitics. I'm interested in this portion of the CREATE:

WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT |

ROWS } ] [ , ] ]
[ NORECOMPUTE ]

Thanks, MarkAny ideas?