Extract a complete XML section and sub sections

Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
You can specify relative XPaths for the columns in the subelements, as shown
below. Is that what you mean?
DECLARE @.TESTXML nvarchar(2000)
DECLARE @.hDoc integer
SET @.TESTXML =
'<Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>'
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header', 1)
with
(reportType varchar(10),
reportNumber VarChar(6),
batchNumber varchar(6),
reportSequenceNumber varchar(6),
userNumber varchar(6),
ProducedOnTime nvarchar(10) 'ProducedOn/@.time',
ProducedOnDate nvarchar(20) 'ProducedOn/@.date',
ProcessingDate nvarchar(20) 'ProcessingDate/@.date' )
EXEC sp_xml_removedocument @.hDoc
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4CD137BC-09E8-42A8-BC86-6C6991EA979C@.microsoft.com...
Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
sql

Express Manager

Hi all,
I've just downloaded SQL Server 2005 Express and I'm wondering what's
happened to Express Manager? Apparently it's meant to be available as
a separate download? Is it even available yet and if not when's it
likely to be out?
Cheers,
Simon
hi Simon,
"Simon Johnson" <simon.johnson@.gmail.com> ha scritto nel messaggio
news:f03d8ae0.0409050700.59b2916d@.posting.google.c om...
> Hi all,
> I've just downloaded SQL Server 2005 Express and I'm wondering what's
> happened to Express Manager? Apparently it's meant to be available as
> a separate download? Is it even available yet and if not when's it
> likely to be out?
eXpress Manager will be available for download in the next future... at the
moment you can resort on a free Query Analyzer replacement you can get for
free at http://www.tegels.org/qara/#dl
please post messages about SQLExpress and SQL Server 2005 in the related
beta newsgroup
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> eXpress Manager will be available for download in the next future... at the
> moment you can resort on a free Query Analyzer replacement you can get for
> free at http://www.tegels.org/qara/#dl
> please post messages about SQLExpress and SQL Server 2005 in the related
> beta newsgroup
Thanks very much.. What's the full name to the beta newsgroups?
Cheers,
Simon.
|||hi Simon,
"Simon Johnson" <simon.johnson@.gmail.com> ha scritto nel messaggio
news:f03d8ae0.0409060016.79e31247@.posting.google.c om...[vbcol=seagreen]
the[vbcol=seagreen]
for
> Thanks very much.. What's the full name to the beta newsgroups?
>
please go to
http://communities.microsoft.com/new...r2005&slcid=us
additional inforamation are provided to access the NGs via Outlook too
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,
Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,