sql database integrity: creating

Showing posts with label creating. Show all posts

Tuesday, March 27, 2012

Creation of aligned partitioned indexes

Hi All,

In the manual I find the following comment for creating indexes.

"If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table."

We are creating some dimensional models in SQL Server with about 100M rows in the largest fact tables.

What I have created are base tables which were on 'primary' and then created a clustered index over a partitioning scheme. I did this as I figured the data will go into the clustered index in any case.

However, when I then create indexes on these tables they do not look like they are petitioned....at least not as far as I can tell.

Q1. Is there some way to tell if the index was partitioned properly?

Q2. If the index is not partitioned is there any simply way to alter the table to the partitioning scheme? My reading of the manual tells me I have to unload, truncate, alter and then reload the table. Is there a better way?

Thanks in advance for your assistance.

Best Regards

Peter

Hi All,

since I did the work to figure this out I thought I would share.....

If you go into the sql studio and look at the properties of the index you can see if it is partitions and if the partitioning column is there.

There is no way I have found to alter the table to the partitioning scheme. However, if you create the index and make the partitioning column the first element of the index and use the partitioning scheme for the table it will have exactly the same effect as partitioning the table and then providing no on clause in the create index.

So you have a choice of specifiying the on clause in the index create or letting sql server default and do this for you......I guess it is a personal opinion as to whether one wants explicit control or default control over creating indexes.....also, even if the table is partitioned if you use an on clause in the index you can partition the index and place it onto the file groups you want to....

Oh the joy of setting up partitioned indexes etc.. :-)

Peter

|||

And now I have one more question on this topic....

In oracle it is possible to partition indexes and have them be 'local' in that they are only related to the partition to which they point.

The reason for doing this is when loading the data the local bit mapped indexes are dropped and once the days transactions/summaries are loaded the local bit mapped indexes can be put back...so you are only every rebuilding the indexes for the data that changed and it is only one partition that you are pointing to for that bit map rebuild...this is is really handy because of the cost of index rebuilds. So you can easily put a few hundred million rows into a partition with little difficulty when doing it this way...

I wondered if SQL Sever had the abiity yet to remove just the portion of a partitioned index pointing to the current partition to have rows inserted/updated and then allowed that index to be rebuilt or updated after updates table place...What I want to avoid is the dropping and recreation of all the query indexes on the table because that would rebuild the index for all partitions and not just the one...

Can this be done? Can anyone point me to the relevant documentation on how to do this?

Thanks

Peter

|||

Hi All,

as we move forward with testing we had one other question we thought someone here might have seen before we test it out ourselves.

We have created aligned partitioning indexes for each table with each index containing the partitioning column and one dimension table key.

We have placed these indexes onto the same partitioning scheme as the table and therefore the same file groups and the table inside the clustered index.

We suspect that performance will be improved if we place the indexes onto their own file groups....but we wonder 'by how much' and 'is it worth it'.

Has anyone else out there performed testing on partitioned tables and aligned indexes both on the same file groups and on different file groups and have an idea of the difference in performance?

Thanks

Peter

Creating/Running DTS Packages with MSDE

Hi,
Is it possible to create DTS packages with MSDE? If so, how?
Would you execute them via the "dtsrun" command-prompt utility?
Thanks very much,
Mark Holahan
MSDE does not come with the tools that are necessary to create DTS packages.
For that you will need to user Enterprise Manager, which come with the full
versions of SQL Server, including the Developers version. MSDE instances can
store DTS packages in their repository and MSDE 2000 comes with the
dtsrun.exe utility, allowing you to execute packages.
Jim
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
news:e93IFVWBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to create DTS packages with MSDE? If so, how?
> Would you execute them via the "dtsrun" command-prompt utility?
> Thanks very much,
> Mark Holahan
>

Creating/Normalizing a database

Sorry if this is the incorrect area to ask this question [for my solution].
I'm NOT a SQL programmer but a pretty good layman.
I'd like to create a test database. I've been wondering if my thinking is
correct in normalizing it.
Table Questions
QID [pk]
QText
Table Answers
AnsID [pk]
QID [fk]
Ans
Table CorrectAnswers
AnsID [pk]
QID [fk]
-- OR --
One [flat] table for it all. Obviously there will be nulls here, especially
for T/F.
Table Test
QID [pk]
QText
Answer0
Answer1
Answer2
Answer3
Answer4
Answer5
Answer6
Answer7
Answer8
Answer9
AnswerCorrect (should this contain the text of the correct answer or the
Field Name of the correct answer?)
Now... I have 100 questions and want to extract 25% randomly. I need a
randomizer for a for loop to not ONLY randomize the questions, but randomize
the non T/F answers if it is multiple choice. (which begs a question, should
I have a field that specifies it as a T/F [Yes/No] answer or multiple
choice?)
What would my SQL statement look like?
Anybody who can answer this will ultimately write my entire logic for my
task and I thank you.
Jon| I'd like to create a test database. I've been wondering if my thinking is
| correct in normalizing it.
|
| Table Questions
| QID [pk]
| QText
|
| Table Answers
| AnsID [pk]
| QID [fk]
| Ans
|
| Table CorrectAnswers
| AnsID [pk]
| QID [fk]
|
| -- OR --
| One [flat] table for it all. Obviously there will be nulls here,
especially
| for T/F.
|
| Table Test
| QID [pk]
| QText
| Answer0
| Answer1
| Answer2
| Answer3
| Answer4
| Answer5
| Answer6
| Answer7
| Answer8
| Answer9
| AnswerCorrect (should this contain the text of the correct answer or the
| Field Name of the correct answer?)
|
| Now... I have 100 questions and want to extract 25% randomly. I need a
| randomizer for a for loop to not ONLY randomize the questions, but
randomize
| the non T/F answers if it is multiple choice. (which begs a question,
should
| I have a field that specifies it as a T/F [Yes/No] answer or multiple
| choice?)
|
| What would my SQL statement look like?
|
| Anybody who can answer this will ultimately write my entire logic for my
| task and I thank you.
--
Hi Jon,
If you have one and only one correct answer for each question, then I would
design your tables as follows:
Tbl_Questions
Question_no (primary key)
Question_Text
Question_CorrectAnswer
Tbl_PossibleAnswers
Question_no (foreign key)
PossibleAnswer_no
PossibleAnswer_Description
There will be a one-to-many relationship between tbl_Questions and
tbl_PossibleAnswers.
Hope this helps,
--
Eric Cárdenas
SQL Server support

Creating...Connecting... i'm missing something...

I'm creating a data storage program in VB 2005 Express. it works perfectly, and installs and everything, but it stores the database in an obscure data file in the user's local settings folder as an mdf file.

once that's created, i want to create an access front-end to view the data, but can't get it to work...

when i try to create linked tables, i establish the connection to sql server (2005 express) through the wizards, but the database i want to connect to isn't in the list of DBs, so i can't connect to it. There's an option to add a database, but it just asks for the name, and i don't know what to tell it, because the file i want is in the obscure data file, and i don't actually know what it's asking for. full path? just file name? no idea...

Here's all the other ways i can think of to add it to the list of DBs in my instance of SQL...

if i open Express Manager to edit the databases in the instance of SQL server, i can create new databases, but i can't seem to add existing databases to SQL Server's list of databases in the instance.

I can use the instance of SQL server to connect to the DB through VB 2005 express's database explorer, but once i close it down, the connection's gone.

when the program is packaged and installed, it automatically creates the link to the database, and it isn't added to SQL Server's list either.

what am i missing? how can i get my Access FE to connect properly to the mdf BE?

Can anyone help me?I think you have ended up using something called user instances, these are dynamically brought online at connect time, to connect to the database from Access it needs to be attached to SQL Server as a regular database.

Lookup sp_attach... in the help file. I also suggest moving the database to the data directorty for SQL Server

Creating your own web-part?

I'm doing research on SQL Reporting Services 2005 and saw that for
integration w/ SharePoint Services, Reporting Services had two standard
web-parts. Is it possible to create your own web-part or customize the
existing web parts, using Reporting Services for SharePoint?
Thanks!Amita,
You can write using C# preferably your own. For more info
Visit http://blogs.sqlxml.org/bryantlikes/ Good one.
Amarnath
"amita.sanghani@.gmail.com" wrote:
> I'm doing research on SQL Reporting Services 2005 and saw that for
> integration w/ SharePoint Services, Reporting Services had two standard
> web-parts. Is it possible to create your own web-part or customize the
> existing web parts, using Reporting Services for SharePoint?
> Thanks!
>|||Thanks!

Creating your first data warehouse

Hello,
I am doing a research project on data warehousing so that I can see
what it can do for my workplace. I have googled on this subject and
the web sites I found relating to this have not been too detailed. I
have a basic set of data and business questions that I want answered
already scoped out and I just wanted some rough guidelines as to what
common processes go into creating your first data warehouse. So far it
seems like a starting point is to grab the raw data that is of interest
and push it to a data warehouse, perhaps aggregating data as it is
moved. Past that point I am a little vague on creating fact tables and
dimensions. Does anybody have some sagely advice, or links to sites
that are a good starting point for someone wanting to throw together a
data warehouse?weluvpaul@.hotmail.com wrote:
> Hello,
> I am doing a research project on data warehousing so that I can see
> what it can do for my workplace. I have googled on this subject and
> the web sites I found relating to this have not been too detailed. I
> have a basic set of data and business questions that I want answered
> already scoped out and I just wanted some rough guidelines as to what
> common processes go into creating your first data warehouse. So far it
> seems like a starting point is to grab the raw data that is of interest
> and push it to a data warehouse, perhaps aggregating data as it is
> moved. Past that point I am a little vague on creating fact tables and
> dimensions. Does anybody have some sagely advice, or links to sites
> that are a good starting point for someone wanting to throw together a
> data warehouse?
Buy "The datawarehouse etl toolkit" and "the data warehouse toolkit"
from amazon.
Very good books that will help you understand the whole concept.|||Hi weluvpaul@.hotmail.com,
feel free to browse all the materials on my web
site...www.peternolan.com. It is there for 'newbies'. Feel free to go
to the beginners page. It points you to books I recommend.
I would recommend you do not try to 'read the books and then build a
DW'. Thats rather a slow way to go about getting a DW built.....If
your company truely has no DW already I'd suggest you look outside for
some skills/experience to show you the way.
On 'What can a DW do for my business?'
For commercial organisations the answer should be 'make more money'...
Or more correctly 'enable the business to increase profitability on a
long term sustainable basis.'
Alas, this does not seem to be what most DWs get built for...
Browse through my 'Newsletters' and especially the one about Business
Benefits of DWing.
On my 'fastest payback' project we got all our money back on the EDW
and more before we even turned it on......
So far I haven't heard of any other project that paid back before it
was finished!!
Best Regards
Peter Nolan
www.peternolan.com
weluvpaul@.hotmail.com wrote:
> Hello,
> I am doing a research project on data warehousing so that I can see
> what it can do for my workplace. I have googled on this subject and
> the web sites I found relating to this have not been too detailed. I
> have a basic set of data and business questions that I want answered
> already scoped out and I just wanted some rough guidelines as to what
> common processes go into creating your first data warehouse. So far it
> seems like a starting point is to grab the raw data that is of interest
> and push it to a data warehouse, perhaps aggregating data as it is
> moved. Past that point I am a little vague on creating fact tables and
> dimensions. Does anybody have some sagely advice, or links to sites
> that are a good starting point for someone wanting to throw together a
> data warehouse?|||Thanks for the advice guys, I will check out the references!

Creating xsd schema file for a xml file ...

Hi ...
I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question).
The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also extract the attributes from the ATMOSPHERIC element ... ? Not sure about the schema file ?
Thanks,
Chris

////////////////////////////////////////////////////
// schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>
<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>
<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

////////////////////////////////////////////////////
// xml data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

////////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestRWISRawAtmospheric] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),
[ProductInstanceId] [char] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT ('5abbbc86-fb2c-4703-9589-b55f763ee150'),
[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObsDateTime] [datetime] NULL ,
[InsertDateTime] [datetime] NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime] DEFAULT (getdate()),
[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelativeHumidity] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPDGust] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMin] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRAvg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMax] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipIntensity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipType] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipRate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pressure] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visability] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMax] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMin] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WetBulbTemp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastPrecipStart] [datetime] NULL ,
[LastPrecipEnd] [datetime] NULL ,
[PrecipAccum1Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum3Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum6Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum12Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum24Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED
(
[RecordId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...

Sorry about the confusion. I was having some issues with bulk

loading using the IDENTITY Column in the target table. I was

setting the COM attribute KeepIdentity to TRUE rather than FALSE.

The schema and xml are correct.

Thanks,

Chris

Creating xsd schema file for a xml file ...

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...
Sorry about the confusion. I was having some issues with bulk loading using the IDENTITY Column in the target table. I was setting the COM attribute KeepIdentity to TRUE rather than FALSE.
The schema and xml are correct.
Thanks,
Chris

Creating XML using For XML Explicit

Hi,
I am stuck and I thought somebody can help me here. I am trying to
construct a query from a a table to create an XML result. My table has
the following columns
| Book | Author | Chapter | Page | URL | Description |
I am trying to get it to return
Any ideas on how to construct the query?
Thanks,
Mo
<?xml version="1.0" encoding="utf-8" ?>
<siteMap>
<siteMapNode title="Home" url="~/home.aspx" description="Go To
Home">
<siteMapNode title="Book 1" url="~/book1/book1.aspx"
description="Go To Book 1">
<siteMapNode title="Chapter 1" url="~/book1/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book1/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book1/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book1/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page1/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page1/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page1/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page2/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page2/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page2/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book1/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book1/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Book 2" url="~/book2/book2.aspx"
description="Go To Book 2">
<siteMapNode title="Chapter 1" url="~/book2/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book2/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book2/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book2/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book2/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book2/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMap>
> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
You could try something like this
Select a.Title as "@.Title" , a.Url as "@.Url", a.description as "@.Description
",
cast((Select b.Title as "@.Title" , b.Url as "@.Url" , b.description as "@.De
scription" from sitemap b
where b.parent = a.id
for xml Path('siteMapNode')) as xml)
from sitemap a
where a.parent is null order by a.id
for Xml path ('siteMapNode'), root('siteMap')
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities|||Hi Mo
Did Gary's answer using the easier to use FOR XML PATH mode in SQL Server
2005 help or do you need the explicit mode solution?
Thanks
Michael
"Mo" <le_mo_mo@.yahoo.com> wrote in message
news:1172481947.758227.123760@.k78g2000cwa.googlegroups.com...
> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
>

Creating XML using For XML Explicit

Hi,
I am stuck and I thought somebody can help me here. I am trying to
construct a query from a a table to create an XML result. My table has
the following columns
| Book | Author | Chapter | Page | URL | Description |
I am trying to get it to return
Any ideas on how to construct the query?
Thanks,
Mo
<?xml version="1.0" encoding="utf-8" ?>
<siteMap>
<siteMapNode title="Home" url="~/home.aspx" description="Go To
Home">
<siteMapNode title="Book 1" url="~/book1/book1.aspx"
description="Go To Book 1">
<siteMapNode title="Chapter 1" url="~/book1/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book1/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book1/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book1/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page1/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page1/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page1/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page2/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page2/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page2/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book1/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book1/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Book 2" url="~/book2/book2.aspx"
description="Go To Book 2">
<siteMapNode title="Chapter 1" url="~/book2/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book2/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book2/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book2/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book2/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book2/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMap>

> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
You could try something like this
Select a.Title as "@.Title" , a.Url as "@.Url", a.description as "@.Description",
cast((Select b.Title as "@.Title" , b.Url as "@.Url" , b.description as "@.Description" from sitemap b
where b.parent = a.id
for xml Path('siteMapNode')) as xml)
from sitemap a
where a.parent is null order by a.id
for Xml path ('siteMapNode'), root('siteMap')
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
|||Hi Mo
Did Gary's answer using the easier to use FOR XML PATH mode in SQL Server
2005 help or do you need the explicit mode solution?
Thanks
Michael
"Mo" <le_mo_mo@.yahoo.com> wrote in message
news:1172481947.758227.123760@.k78g2000cwa.googlegr oups.com...
> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
>

Creating XML output...unusual/impossible format?

Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!You cannot achieve this with a single SELECT statement because you can have only one column name for the entire result set.(you stated this already). If you need to have the column value as a tag then a stored procedure is the way to go where you can generate your own XML TAGs.
Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!|||Thanks for the reply.

I was afraid of that. The previous version of the product used a C++ program to generate the XML (or pseudo-XML) output. I was hoping it was just because the developer didn't know how to use the XML extraction stuff in SQL Server. *sigh*

Oh well, I guess I get to gain some C# practice, as the C++ application has the source of the data "hard coded" in the logic, and we need to use a predefined, "standard" linked server reference (aka "alias").|||"Can't be Done"?

The three words I tell developers to never let me hear, otherwise I bring out the baseball bat

Dude, you know the drill

DDL, DML, I gues we got the expected results already...|||Never mind...but I'm only guessing...

USE Northwind
GO

CREATE TABLE myTable99(FundSymbol char(5), FundName varchar(255))
GO

INSERT INTO myTable99(FundSymbol, FundName)
SELECT 'AMRGX', 'AMERICAN GROWTH D' UNION ALL
SELECT 'AHERX', 'AHERX HERITAGE FUND' UNION ALL
SELECT 'AMRVX', 'AMERICAN INVESTORS GROWTH FUND'

SELECT XML_Output
FROM (
SELECT '<?xml version="1.0" encoding="utf-8"?>' AS XML_Output, 1 AS XML_Group, Null AS FundSymbol
UNION ALL
SELECT '<FUNDS>'AS XML_Output, 2 AS XML_Group, Null AS FundSymbol
UNION ALL
SELECT REPLICATE(' ',20)+'<'+FundSymbol+'>'+CHAR(13)+CHAR(10)
+ REPLICATE(' ',40)+'<NAME>'+FundName+'</NAME>'+CHAR(13)+CHAR(10)
+ REPLICATE(' ',20)+'</'+FundSymbol+'>'+CHAR(13)+CHAR(10)AS XML_Output
, 3 AS XML_Group, FundSymbol
FROM myTable99
UNION ALL
SELECT '<FUNDS>'AS XML_Output, 4 AS XML_Group, Null AS FundSymbol
) AS XXX
ORDER BY XML_Group, FundSymbol
GO

DROP TABLE myTable99
GO|||*sigh* What can I say? Every day you guys amaze me more-n-more :)

That's EXACLY what I was a-lookin for! I appreciate you doing my homework for me *hanging head* I tend to forget the usefullness of UNION, and have yet to use "REPLICATE", so now I need to go look THAT one up in BOL so that I can once again expand my horizons just a little more.

Funny (to me, anyway) that because every other application in our world here that creates XML output uses the FOR XML directive to create the output, that I get tunnel vision and just can't seem to see the easy answer lies in "standard" SQL. I guess kinda like my kids, who can't seem to get their heads around the ideas that (a) you can make your own ice cream, (b)clothes can come from a sewing machine instead of a store, (c) there's a way to change your oil without going to Jiffy Lube, and myriad other such examples of "OMG, you mean you can do that the old-fashioned way?".

But I digress...Thanks once again, Brett, for taking the time and effort to plainly show how I can get around yet another stumbling block.

It's too bad though, that since it can't be done, I will have to disregard your fantasy code and open my C# book. ;)

Thanks again!|||A mere trifle...

I was interested, because of a discussion I had with a Java developer.

If I could deliver the content in a manner like you are looking for, then the development life cycle for java gets cut in half.

I'm just exploring this now, but I was leaning on having metadata stored in the database, where I could configure all of the results via table.

Each element would have it's own properties stored.

So if the don't like the text to be black, I can make it red, on the fly. No application release headaches.

I was going to extend that and see if I could also incorporate the style sheets in the database as well..

FOR XML...what a bunch of crap

creating xml file from sql server

i am very new at this xml thing, however, i know how to develop sql. i woul
d
like to write a simple sql statement (using one table) and generate/create a
n
XML FILE that i can save onto my desktop. does anyone have any advice'I have had limited success by doing...
osql -E -S(servername) -d(database name) -H-1 -Q "select * from tablename
for xml auto" -ooutput.xml
But the XML generated is rough. So therefore I posted a similiar question,
amazingly within minutes of yours...
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i wo
uld
> like to write a simple sql statement (using one table) and generate/create
an
> XML FILE that i can save onto my desktop. does anyone have any advice'|||Write an ADO or ADO.net based client program that opens a connection, sends
a FOR XML query over the command stream object, sets the root property on
the result stream and then pipes the result into a file.
HTH
Michael
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>i am very new at this xml thing, however, i know how to develop sql. i
>would
> like to write a simple sql statement (using one table) and generate/create
> an
> XML FILE that i can save onto my desktop. does anyone have any advice'|||See "Retrieving and Writing XML Data" in Sql Server books online.
Also, www.sqlxml.org has some good information and examples.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||Try this link...
http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i wo
uld
> like to write a simple sql statement (using one table) and generate/create
an
> XML FILE that i can save onto my desktop. does anyone have any advice'|||thanks to www.sqlxml.org this example works great:
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from table for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "all my connection parameters"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
Set oCmd = Nothing
%>
so once i have done this...how do you pipe the results into a file? thanks
for your patients and help. also, this takes forever to run because there
are about 50,000 records being returned...do you have any ideas for speedin
g
it up?
"Michael Rys [MSFT]" wrote:

> Write an ADO or ADO.net based client program that opens a connection, send
s
> a FOR XML query over the command stream object, sets the root property on
> the result stream and then pipes the result into a file.
> HTH
> Michael
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>
>|||thanks...that helped a lot...now, i just need to see some examples that pipe
the results to file. got anything up your sleeve? =)
"Chris" wrote:
> Try this link...
> http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
> "snickerskid" wrote:
>|||Here are some examples:
http://www.sqlteam.com/Forums/topic...D=5&CAT_ID=3&To
pic_Title=SQL+Server+2000+XML&Forum_Title=Developer
http://www.sqlxml.org/faqs.aspx?faq=29
Andrew Conrad
Microsoft Corp

creating xml file from sql server

i am very new at this xml thing, however, i know how to develop sql. i would
like to write a simple sql statement (using one table) and generate/create an
XML FILE that i can save onto my desktop. does anyone have any advice?
I have had limited success by doing...
osql -E -S(servername) -d(database name) -H-1 -Q "select * from tablename
for xml auto" -ooutput.xml
But the XML generated is rough. So therefore I posted a similiar question,
amazingly within minutes of yours...
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i would
> like to write a simple sql statement (using one table) and generate/create an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||Write an ADO or ADO.net based client program that opens a connection, sends
a FOR XML query over the command stream object, sets the root property on
the result stream and then pipes the result into a file.
HTH
Michael
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>i am very new at this xml thing, however, i know how to develop sql. i
>would
> like to write a simple sql statement (using one table) and generate/create
> an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||See "Retrieving and Writing XML Data" in Sql Server books online.
Also, www.sqlxml.org has some good information and examples.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Try this link...
http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
"snickerskid" wrote:

> i am very new at this xml thing, however, i know how to develop sql. i would
> like to write a simple sql statement (using one table) and generate/create an
> XML FILE that i can save onto my desktop. does anyone have any advice?
|||thanks to www.sqlxml.org this example works great:
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from table for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "all my connection parameters"
oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
Set oCmd = Nothing
%>
so once i have done this...how do you pipe the results into a file? thanks
for your patients and help. also, this takes forever to run because there
are about 50,000 records being returned...do you have any ideas for speeding
it up?
"Michael Rys [MSFT]" wrote:

> Write an ADO or ADO.net based client program that opens a connection, sends
> a FOR XML query over the command stream object, sets the root property on
> the result stream and then pipes the result into a file.
> HTH
> Michael
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:10A92994-301F-4AF2-80DF-1F8E521F7758@.microsoft.com...
>
>
|||thanks...that helped a lot...now, i just need to see some examples that pipe
the results to file. got anything up your sleeve? =)
"Chris" wrote:
[vbcol=seagreen]
> Try this link...
> http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp
> "snickerskid" wrote:
|||Here are some examples:
http://www.sqlteam.com/Forums/topic...=5&CAT_ID=3&To
pic_Title=SQL+Server+2000+XML&Forum_Title=Develope r
http://www.sqlxml.org/faqs.aspx?faq=29
Andrew Conrad
Microsoft Corp

Creating XML Document from SQL Server

Would anyone please help me , how to create XML document
for a specific record from the SQL Server database.
Is there any builtin tool available. If so, how to access
it.
Would I need to create a program to generate XML
document. If so, could you suggest the development
package/code examples...
Appreciate the info.
Would something along the line of
select * from T for xml auto
or
select * from T for xml raw
help?
Best regards
Michael
"Sat" <anonymous@.discussions.microsoft.com> wrote in message
news:442c01c42b8b$f0669640$a001280a@.phx.gbl...
> Would anyone please help me , how to create XML document
> for a specific record from the SQL Server database.
> Is there any builtin tool available. If so, how to access
> it.
> Would I need to create a program to generate XML
> document. If so, could you suggest the development
> package/code examples...
> Appreciate the info.

creating xml and passing it on.

I'm rather new to xml in sql server 2000.
So i searched around for a while, but i can't quite understand how should i
make a stored procedure, that will create xml from a select clause and then
pass it as an input parameter to another stored procedure?
Basically i can't figure out how can i use "for xml" other than stored
procedure resultset. Or it is not possible otherwise?
Thanks in advance,
Martin.XML capabilities in SQL 2000 is limited compared to what is offered in SQL
2005. If you are starting out, spend some time at www.sqlxml.org and you'll
find most answers to your questions there.
Anith

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Hi, all!

I am writing the VDI application to allow backup/restore MS SQL Server 2005. I would want to backup/restore remote servers as well as local. I have local instance of MS SQL 2005 and remote. Local instance has MSSQL2005_ZORG instance name, remote uses default (so it supposed to be MSSQLSERVER).

While connection to those server via SQL Server Management studio i see local server as "ZORG\MSSQL2005_ZORG" and remote as "VM2000SRVZ2".

When i try to create VIrtualDeviceSet via CreateEx i pass "MSSQL2005_ZORG" as lpInstanceName parameter and all works fine. But I could't create same device set for remote 'MSSQLSERVER' instance. I passed any combination for that, such as "VM2000SRVZ2\MSSQLSERVER", "MSSQLSERVER", "VM2000SRVZ2", "\\VM2000SRVZ2\MSSQLSERVER" and so on. No luck always get VD_E_INSTANCE_NAME (0x80770007).

Any idea?

--Thanks

Hi,
As per the Microsoft Virtual Backup specifications, VDI can be used only on local machines.You can download the specifications from

http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

If u have used VDI for some time, i need ur help.Relpy if you can

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Hi, all!

While connection to those server via SQL Server Management studio i see local server as "ZORG\MSSQL2005_ZORG" and remote as "VM2000SRVZ2".

Any idea?

--Thanks

Hi,
As per the Microsoft Virtual Backup specifications, VDI can be used only on local machines.You can download the specifications from

http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

If u have used VDI for some time, i need ur help.Relpy if you can

Creating views under INFORMATION_SCHEMA in SQL 2005

Hello to everyone,

I have a couple of useful views created under INFORMATION_SCHEMA in SQL 2000. Now I am trying to create them in SQL 2005 environment. As an example simply run the following code as "sa" in SQL 2005 (it works in SQL 2000):

CREATE VIEW INFORMATION_SCHEMA.MyTABLES
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

In my environment I received the following error:

Msg 2760, Level 16, State 1, Procedure MyTABLES, Line 3
The specified schema name "INFORMATION_SCHEMA" either does not exist or you do not have permission to use it.

I tried to create the view under other schema and change the owner to INFORMATION_SCHEMA and some other techniques that worked in SQL 2000 (as a direct update of sysobjects). Also I tried (using the system stored procedures as well as the GUI) to change the login/user/schema chain in order to overcome the problem with no success. Please let me know if you can run the above code successfully or if you have any other suggestions.

Peter Petrov

Hi,

The information views are not intended to be extendible. The Information Schema is an ANSI specification for obtaining metadata from your database. Since it is a standard it is ment to be used across multiple types of databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thank you, Geert.

We all know that but sometimes as we also know we need some of the benefits of the system objects for temporary “relief”. So the question is not “To do or not to do that?” but “How we can do that?”. That’s why the “back doors” exist J

Tuesday, March 27, 2012

Creation of aligned partitioned indexes

Creating/Running DTS Packages with MSDE

Creating/Normalizing a database

Creating...Connecting... i'm missing something...

Creating your own web-part?

Creating your first data warehouse

Creating xsd schema file for a xml file ...

Creating xsd schema file for a xml file ...

Creating XML using For XML Explicit

Creating XML using For XML Explicit

Creating XML output...unusual/impossible format?

creating xml file from sql server

creating xml file from sql server

Creating XML Document from SQL Server

creating xml and passing it on.

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Creating views under INFORMATION_SCHEMA in SQL 2005

sql database integrity

Blog Archive

About Me