How to Flatten ?

Is there an easy to understand method of bringing this into a flat structure
(all elements related to a line item on one line) ? I do not care if the
xsl is "optimal" with respect to efficiency, I just want it to work with as
little complexity as possible. Please note the multiple line items. I
have seen and understand examples where there is a one to one relationship
between the header and the detail line, but not when there are 3 levels
involved and multiple detail lines.
Thanks,
Rob
<Invoices>
<Invoice id="ABC">
<Supplier>123</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>2.88</NetAmount><Codes><Code1>ABC</Code1><Code2>DEF</Code2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>1.88</NetAmount><Codes><Code1>VBH</Code1><Code2>LKJ</Code2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
<Invoice id="DEF">
<Supplier>568</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>7.00</NetAmount><Codes><Code1>VPP</Code1><Code2>LRE</Code2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>9.00</NetAmount><Codes><Code1>ZAQ</Code1><Code2>WIJ</Code2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
</Invoices>
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:%KB7d.14101$yp.4743@.bignews1.bellsouth.net...
> Is there an easy to understand method of bringing this into a flat
structure
> (all elements related to a line item on one line) ? I do not care if
the
Rob,
Does this work for you:
DECLARE @.xml varchar(2000)
set @.xml =
'<Invoices>
<Invoice id="ABC">
<Supplier>123</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>2.88</NetAmount><Codes><Code1>ABC</Code1><Code2>DEF</Cod
e2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>1.88</NetAmount><Codes><Code1>VBH</Code1><Code2>LKJ</Cod
e2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
<Invoice id="DEF">
<Supplier>568</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>7.00</NetAmount><Codes><Code1>VPP</Code1><Code2>LRE</Cod
e2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>9.00</NetAmount><Codes><Code1>ZAQ</Code1><Code2>WIJ</Cod
e2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
</Invoices>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc output, @.xml
SELECT * FROM OPENXML(@.hdoc, '/Invoices/Invoice/InvoiceItems/InvoiceItem',
2)
WITH (
InvoiceId char(3) '../../@.id',
Supplier varchar(20) '../../Supplier',
line char(1) '@.line',
Code1 char(3) 'Codes/Code1',
Code2 char(3) 'Codes/Code2',
NetAmount numeric(9,2) 'NetAmount')
EXEC sp_xml_removedocument @.hDoc
sql

How to find query plan for a stored procedure using temporary tables

This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro

--***********************************
use pubs
go
CREATE PROCEDURE Test @.percentage int
AS
SET Nocount on
--Create and load a temporary table
select * into #Temp1 from titleauthor

--Create second temporary table
create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord
int, rolaylityper int)

--load the second temporary table from the first one
insert into #Temp2 select * from #Temp1

go

set showplan_Text ON
go
EXEC Test @.percentage = 100
GO
set showplan_Text OFF
go
**************************************

I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.

I do understand what the error message means. I just want to know a
better way of finding the query plan when using temp objects.
My real production procs are hundreds of lines with many temp tables
used in join with other temp tables and/or real tables.

RegardsOn 25 Oct 2006 15:41:52 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>This post is related to SQL server 2000 and SQL Server 2005 all
>editions.
>Many of my stored procedures create temporary tables in the code. I
>want to find a way to find the query plan for these procs

(snip)

Quote:

Originally Posted by

>I get the following error
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp2'.
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp1'.

Hi comp_databases_ms-sqlserver,

You get these errors because SET SHOWPLAN_TEXT ON tells SQL Server to
generate a plan INSTEAD OF executing the SQL. As a result, your temp
tables are not generated.

The only way to get execution plans is to allow SQL Server to execute
the statements as well as outputting the plan. You do this by issuing
the command
SET STATISTICS PROFILE ON;
Note that this includes other (run-time) info as well as the plan.

Of course, you can also decide to use CREATE TABLE for all temp tables
at the start of your procs instead of using INSERT INTO.

--
Hugo Kornelis, SQL Server MVP|||SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
troubleshoot performance problems of a proc with 2000+ lines of code
that is using 10+ temp tables?
I am not able to configure SQLDebugger from a client. I have to be on
the server to use it. This applies to SQL2000.|||On 26 Oct 2006 11:29:57 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
>troubleshoot performance problems of a proc with 2000+ lines of code
>that is using 10+ temp tables?
>I am not able to configure SQLDebugger from a client. I have to be on
>the server to use it. This applies to SQL2000.

Hi comp_databases_ms-sqlserver,

That's a pretty broad question!

Some of the things I'd look into if I was assigned this task would be
(in random order):

* Try to combine some or even all steps of the procedure into one single
query. Procs like this are often the result of procedural thinking. New
SQL coders with a background in procedural languages often tend to think
in the steps required to get somewhere. They will then code a sequence
of steps, with temp tables to hold intermediate results. A truly
set-based and declarative solution gives the optimzer more freedom to
rearrange steps and reduces the amount of moving data around. This can
yield huge benefits in performance.

* Create temp tables at the start of the stored proc. This reduces the
number of recompiles (and at 2000+ lines, recompiling the proc will
probably take a noteable amount of time). It also gives you the
opportunity to declare indexes on the temp tables BEFORE data is put
into them - this will reduce the number of recompiles even further and
it may speed up execution. However, it can also sometimes be better to
postpone index creation until after the temp table is populated, even
though this means accepting a recompilation. Test various strategies to
find out.

* Copy the code from the stored procedure to Query Analyzer and run it
one step at a time, using BEGIN TRAN, ROLLBACK and COMMIT as needed to
be able to repeat each step multiple times. This gives you the option to
get an execution plan for each step, and also to try different versions
of the query and/or different indexes to see how they change the
execution speed of that particular part of the proc.

* Setup a profiler trace, run the stored proc, then use the output from
the profiler trace to identify which part(s) of the stored proc are
responsible for the largest portion of the execution time.

* Check if your procedure might be subject to parameter sniffing (google
for it if you've never heard of the term).

* If you really can't combine the steps, consider breaking the procedure
in several smaller parts. This reduces compilation time when
recompilations are needed and can be leveraged to solve parameter
sniffing problems.

There are probably more things you can do, but these are the ones I can
think of at the top of my head.

Good luck!

--
Hugo Kornelis, SQL Server MVP

How to find data/text within SQL DB

Hi there, posted the same question in FULLTEXT group, but I think this group
could also help me, cos it is not just FULLTEXT related
I have a problem and don't know how to resolve it. I had sharepoint portal
server 2003, then it was upgraded to 2007, but after that two meeting
workspace been lost. Lost from sharepoint site, but not from the DB. Wrote
to sharepoint group and one guy just started with step buy step guide about
how could I find it within SQL DB
http://www.developerfood.com/lost-meeting-workspace-after-2003to2007-inplace-upgrade/microsoft-public-sharepoint-portalserver/44bdf402-c511-45a8-8452-bb1d95cb2908/article.aspx
but he never replied me
Please could some one help me with that?
Thank you in advance
Hi
"Guzun, Alex" wrote:

> Hi there, posted the same question in FULLTEXT group, but I think this group
> could also help me, cos it is not just FULLTEXT related
> I have a problem and don't know how to resolve it. I had sharepoint portal
> server 2003, then it was upgraded to 2007, but after that two meeting
> workspace been lost. Lost from sharepoint site, but not from the DB. Wrote
> to sharepoint group and one guy just started with step buy step guide about
> how could I find it within SQL DB
> http://www.developerfood.com/lost-meeting-workspace-after-2003to2007-inplace-upgrade/microsoft-public-sharepoint-portalserver/44bdf402-c511-45a8-8452-bb1d95cb2908/article.aspx
> but he never replied me
> Please could some one help me with that?
> Thank you in advance
>
All the posts don't seem to be available! http://tinyurl.com/yr38vs shows
more replies. I don't know sharepoint so I am not sure what should be in the
sites table, but if you could restore a copy of the database to a different
server (or under a different database name on the same server) you will be
able to compare the contents and see if they are different.
John

How to find data/text within SQL DB

Hi there, posted the same question in FULLTEXT group, but I think this group
could also help me, cos it is not just FULLTEXT related :)
I have a problem and don't know how to resolve it. I had sharepoint portal
server 2003, then it was upgraded to 2007, but after that two meeting
workspace been lost. Lost from sharepoint site, but not from the DB. Wrote
to sharepoint group and one guy just started with step buy step guide about
how could I find it within SQL DB
http://www.developerfood.com/lost-meeting-workspace-after-2003to2007-inplace-upgrade/microsoft-public-sharepoint-portalserver/44bdf402-c511-45a8-8452-bb1d95cb2908/article.aspx
but he never replied me
Please could some one help me with that?
Thank you in advanceHi
"Guzun, Alex" wrote:
> Hi there, posted the same question in FULLTEXT group, but I think this group
> could also help me, cos it is not just FULLTEXT related :)
> I have a problem and don't know how to resolve it. I had sharepoint portal
> server 2003, then it was upgraded to 2007, but after that two meeting
> workspace been lost. Lost from sharepoint site, but not from the DB. Wrote
> to sharepoint group and one guy just started with step buy step guide about
> how could I find it within SQL DB
> http://www.developerfood.com/lost-meeting-workspace-after-2003to2007-inplace-upgrade/microsoft-public-sharepoint-portalserver/44bdf402-c511-45a8-8452-bb1d95cb2908/article.aspx
> but he never replied me
> Please could some one help me with that?
> Thank you in advance
>
All the posts don't seem to be available! http://tinyurl.com/yr38vs shows
more replies. I don't know sharepoint so I am not sure what should be in the
sites table, but if you could restore a copy of the database to a different
server (or under a different database name on the same server) you will be
able to compare the contents and see if they are different.
John

How to find data/text within SQL DB

Hi there, posted the same question in FULLTEXT group, but I think this group
could also help me, cos it is not just FULLTEXT related
I have a problem and don't know how to resolve it. I had sharepoint portal
server 2003, then it was upgraded to 2007, but after that two meeting
workspace been lost. Lost from sharepoint site, but not from the DB. Wrote
to sharepoint group and one guy just started with step buy step guide about
how could I find it within SQL DB
http://www.developerfood.com/lost-m.../>
rticle.aspx
but he never replied me
Please could some one help me with that?
Thank you in advanceHi
"Guzun, Alex" wrote:

> Hi there, posted the same question in FULLTEXT group, but I think this gro
up
> could also help me, cos it is not just FULLTEXT related
> I have a problem and don't know how to resolve it. I had sharepoint portal
> server 2003, then it was upgraded to 2007, but after that two meeting
> workspace been lost. Lost from sharepoint site, but not from the DB. Wrote
> to sharepoint group and one guy just started with step buy step guide abou
t
> how could I find it within SQL DB
> http://www.developerfood.com/lost-m...
/article.aspx
> but he never replied me
> Please could some one help me with that?
> Thank you in advance
>
All the posts don't seem to be available! http://tinyurl.com/yr38vs shows
more replies. I don't know sharepoint so I am not sure what should be in the
sites table, but if you could restore a copy of the database to a different
server (or under a different database name on the same server) you will be
able to compare the contents and see if they are different.
John