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 the replication frequency

Hello,
We are getting the replication data from some other database. Our
replication method is push method. How to find out the frequency of the
replication process per day? I have to find out this from the subscription
database side.
Regards,
Sunil
Sunil,
this should be what you want:
exec msdb..sp_help_jobschedule @.job_name = 'distributionagentname'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul.
But i don't have access to the distribution server. I am having access to
only subscriber database which is of type push. And in the subcriber side
we don't have jobs for replication in the push type. How to find out now?
Thanks,
Sunil
Message posted via http://www.droptable.com
|||As it is push, you can open up Query Analyser connection to the
publisher/distributor and run the query there. You could also do this
through a linked server sitting on the subscriber. If you don't have any
connectivity at all, then I don't see how this is possible. You could
possibly derive it from the MSsubscription_agents table, but this is messy.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Paul,
I don't have connection info for the distributor server. In the subscriber
database having two tables 'MSreplication_subscriptions' and
'MSsubscription_agents' tables. In the first table, one field is there
namely 'time'. Is this field gives info when the replication occured?
Thanks,
Sunil
Message posted via http://www.droptable.com
|||The lastsynctime in MSsubscription_agents is what I was thinking of - it
doesn't maintain a history, but if you poll it you should get an idea of the
frequency.
Rgds,
Paul Ibison, SQL Server MVP

How to Find Full Text Index Catalog Creation - Time duration.

I Have used full text indexin my project.
I want to find "Full Text Index Catalog Creation" Time duration.
Is there any method to find.
I am using SQL Server 2005

Regards

Vasanth Thangasamy

The start and stop times are logged in the event log. I believe the stop time includes the duration but not sure without checking.