Showing posts with label query. Show all posts

Tuesday, March 27, 2012

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 Views

I get the following error when i create views in a batch
using 'sql query analyser' as I want to create all the
views at one go. Is there solution.
Server: Msg 156, Level 15, State 1, Procedure View1, Line
11
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Procedure View2, Line
11
'CREATE VIEW' must be the first statement in a query batch.
Make sure the=20
GO
keyword appears between each statement
CREATE VIEW <name>=20
AS
SELECT...
GO
CREATE VIEW <name>=20
AS
SELECT...
GO
--=20
Keith
"BILLI98" <billi98@.hotmail.com> wrote in message =
news:423401c42b7e$8b4560f0$a401280a@.phx.gbl...
> I get the following error when i create views in a batch=20
> using 'sql query analyser' as I want to create all the=20
> views at one go. Is there solution.
>=20
>=20
> Server: Msg 156, Level 15, State 1, Procedure View1, Line=20
> 11
> Incorrect syntax near the keyword 'CREATE'.
> Server: Msg 111, Level 15, State 1, Procedure View2, Line=20
> 11
> 'CREATE VIEW' must be the first statement in a query batch.
>=20
>=20
>
|||On Mon, 26 Apr 2004 04:06:37 -0700, BILLI98 wrote:

>I get the following error when i create views in a batch
>using 'sql query analyser' as I want to create all the
>views at one go. Is there solution.
>
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>11
>Incorrect syntax near the keyword 'CREATE'.
>Server: Msg 111, Level 15, State 1, Procedure View2, Line
>11
>'CREATE VIEW' must be the first statement in a query batch.
Just put in a "go" before each CREATE VIEW. Make sure you put GO at
the beginning of the line. Like this:
CREATE VIEW TestView
AS SELECT au_id FROM authors
GO
CREATE VIEW TestView2
AS SELECT au_lname FROM authors
GO
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Views

I get the following error when i create views in a batch
using 'sql query analyser' as I want to create all the
views at one go. Is there solution.
Server: Msg 156, Level 15, State 1, Procedure View1, Line
11
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Procedure View2, Line
11
'CREATE VIEW' must be the first statement in a query batch.Make sure the GO
keyword appears between each statement
CREATE VIEW <name> AS
SELECT...
GO
CREATE VIEW <name> AS
SELECT...
GO
-- Keith
"BILLI98" <billi98@.hotmail.com> wrote in message =news:423401c42b7e$8b4560f0$a401280a@.phx.gbl...
> I get the following error when i create views in a batch > using 'sql query analyser' as I want to create all the > views at one go. Is there solution.
> > > Server: Msg 156, Level 15, State 1, Procedure View1, Line > 11
> Incorrect syntax near the keyword 'CREATE'.
> Server: Msg 111, Level 15, State 1, Procedure View2, Line > 11
> 'CREATE VIEW' must be the first statement in a query batch.
> > >|||On Mon, 26 Apr 2004 04:06:37 -0700, BILLI98 wrote:
>I get the following error when i create views in a batch
>using 'sql query analyser' as I want to create all the
>views at one go. Is there solution.
>
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>11
>Incorrect syntax near the keyword 'CREATE'.
>Server: Msg 111, Level 15, State 1, Procedure View2, Line
>11
>'CREATE VIEW' must be the first statement in a query batch.
Just put in a "go" before each CREATE VIEW. Make sure you put GO at
the beginning of the line. Like this:
CREATE VIEW TestView
AS SELECT au_id FROM authors
GO
CREATE VIEW TestView2
AS SELECT au_lname FROM authors
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Views

I get the following error when i create views in a batch
using 'sql query analyser' as I want to create all the
views at one go. Is there solution.
Server: Msg 156, Level 15, State 1, Procedure View1, Line
11
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Procedure View2, Line
11
'CREATE VIEW' must be the first statement in a query batch.Make sure the=20
GO
keyword appears between each statement
CREATE VIEW <name>=20
AS
SELECT...
GO
CREATE VIEW <name>=20
AS
SELECT...
GO
--=20
Keith
"BILLI98" <billi98@.hotmail.com> wrote in message =
news:423401c42b7e$8b4560f0$a401280a@.phx.gbl...
> I get the following error when i create views in a batch=20
> using 'sql query analyser' as I want to create all the=20
> views at one go. Is there solution.
>=20
>=20
> Server: Msg 156, Level 15, State 1, Procedure View1, Line=20
> 11
> Incorrect syntax near the keyword 'CREATE'.
> Server: Msg 111, Level 15, State 1, Procedure View2, Line=20
> 11
> 'CREATE VIEW' must be the first statement in a query batch.
>=20
>=20
>|||On Mon, 26 Apr 2004 04:06:37 -0700, BILLI98 wrote:

>I get the following error when i create views in a batch
>using 'sql query analyser' as I want to create all the
>views at one go. Is there solution.
>
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>11
>Incorrect syntax near the keyword 'CREATE'.
>Server: Msg 111, Level 15, State 1, Procedure View2, Line
>11
>'CREATE VIEW' must be the first statement in a query batch.
Just put in a "go" before each CREATE VIEW. Make sure you put GO at
the beginning of the line. Like this:
CREATE VIEW TestView
AS SELECT au_id FROM authors
GO
CREATE VIEW TestView2
AS SELECT au_lname FROM authors
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Sunday, March 25, 2012

creating view as a parameter query

Hi,
I created a view in EM and when i right-click to go to the query designer
it allows me to set some parameter but i seem not able to save this as part
of a view ?
how can i do that or is there other alternatives ?
tks & rdgs
You can create a table function to simulate a parameterized view. See "create
function" in BOL.
AMB
"maxzsim" wrote:

> Hi,
> I created a view in EM and when i right-click to go to the query designer
> it allows me to set some parameter but i seem not able to save this as part
> of a view ?
> how can i do that or is there other alternatives ?
> tks & rdgs

creating view as a parameter query

Hi,
I created a view in EM and when i right-click to go to the query designer
it allows me to set some parameter but i seem not able to save this as part
of a view ?
how can i do that or is there other alternatives ?
tks & rdgsYou can create a table function to simulate a parameterized view. See "creat
e
function" in BOL.
AMB
"maxzsim" wrote:

> Hi,
> I created a view in EM and when i right-click to go to the query designer
> it allows me to set some parameter but i seem not able to save this as par
t
> of a view ?
> how can i do that or is there other alternatives ?
> tks & rdgs

creating view as a parameter query

Hi,
I created a view in EM and when i right-click to go to the query designer
it allows me to set some parameter but i seem not able to save this as part
of a view ?
how can i do that or is there other alternatives ?
tks & rdgsYou can create a table function to simulate a parameterized view. See "create
function" in BOL.
AMB
"maxzsim" wrote:
> Hi,
> I created a view in EM and when i right-click to go to the query designer
> it allows me to set some parameter but i seem not able to save this as part
> of a view ?
> how can i do that or is there other alternatives ?
> tks & rdgssql

Creating View

Dear Sender,
I know that this is b'coz of GO statement. but GO is not a
T-SQL command and it works only inside Query analyser. if
the same string i execute through VB code by using
connection.execute, it doesn't recognise GO so i removed
it. It works for creating tables if i remove GO and
execute through VB-code. now i have to run this
query "CREATE VIEW" from my vb code and at one go as i do
for tables, i want to create all the views. ur suggestions
are welcome.
Billi98Hello,
The GO statement only works through osql and isql (or in
this case Query Analyser).
All it does is signal the end of Transact-SQL statements,
including creating views SP or tables.
To be honest I am not too sure what your asking, you do
not actually need it when you are creating a view, but its
a 'nice to have' is you have a script you need running
though QA.
J
>--Original Message--
>Dear Sender,
>I know that this is b'coz of GO statement. but GO is not
a
>T-SQL command and it works only inside Query analyser. if
>the same string i execute through VB code by using
>connection.execute, it doesn't recognise GO so i removed
>it. It works for creating tables if i remove GO and
>execute through VB-code. now i have to run this
>query "CREATE VIEW" from my vb code and at one go as i do
>for tables, i want to create all the views. ur
suggestions
>are welcome.
>Billi98
>
>.
>|||You cannot combine CREATE VIEW with any other command in the same batch ("execution"). So you need to loop the
input file, and each time you find a GO, execute what you have in the buffer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:49f701c42c33$be5f7e20$a601280a@.phx.gbl...
> Dear Sender,
> I know that this is b'coz of GO statement. but GO is not a
> T-SQL command and it works only inside Query analyser. if
> the same string i execute through VB code by using
> connection.execute, it doesn't recognise GO so i removed
> it. It works for creating tables if i remove GO and
> execute through VB-code. now i have to run this
> query "CREATE VIEW" from my vb code and at one go as i do
> for tables, i want to create all the views. ur suggestions
> are welcome.
> Billi98
>

Creating View

Creating Total to-date query...

I am trying to create a query where
the data is for a time period but there is
one column that would show a Total-to-date.

I've tried google and searching this forum
but everyone talks about year-to-date or
period-to-date but I don't want that. I would
like to do :
[from whenever data started being entered]-to-date

I know I can hack this by doing an aggregate like
so:

WITH MEMBER [Timesheet].[Date].[TimeChargedTotal] AS
'Aggregate ( { [Timesheet].[Date].[Year].&[1900].&[1].&[1]:[Timesheet].[Date].[Year].&[2006].&[9].&[15] } )'

Here I am betting on the fact that there is no data before 1-1-1900
which is a safe bet, but I'd prefer to do it the 'right' way if there is one.

Thanks.

If you're using AS 2005, then you can try the shorthand:

{ NULL : [Timesheet].[Date].[Year].&[2006].&[9].&[15] }

|||

Something like [Time].[Date].FirstChild:[Time].[Date].Lastchild might help. Be aware of performance issues with a large dimension.

Regards

Thomas Ivarsson

|||

Thanks guys.

Both answers work.
Is there any performance difference between
using NULL or [Time].[Date].FirstChild as the
beginning of the range?

|||Well, I'm still unclear about the usage scenario - if the calculated member is defined on the [Time].[Date] hierarchy, then on which hierarchy will the user select a time period? If you can translate this to the Adventure Works cube, that would help clarify.|||

I am not familiar enough with adventureworks
to give a proper example.

However I will attempt to explain my scenario.
We have a timesheet system where time spent
on various projects/tasks is collected and then
billed to a client.

A report needs to be created that shows the
previous weeks time (a particualr 1 week period with
a start and end date) spent on a particular project/task
by employee X and the total time spent so far on that
project/task by emplyee X up until the end of that period.
This means that year-to-date (or period-to-date) scenario
does not work because we want ALL time up until
the end of the selected period and there is no guarantee
that a project will last a week, month, year, etc...

Hope that somewhat clarifies the usage scenario.

|||

Okay this is not working the way I anticipated.
I am clearly not understanding MDX and the book
I am refering to:
'SQL Server 2005 Analysis Services 2005 with MDX'
is of no help.

Here is what I am trying to accomplish conceptually:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'AGGREGATE ( {NULL:@.ToTimesheetDate} )'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
([Project].[Project Number].[Project Number].ALLMEMBERS
* [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
}
ON ROWS
FROM
(
SELECT ( STRTOSET(@.ProjectProjectNumber, CONSTRAINED) ) ON COLUMNS
FROM
(
SELECT ( STRTOMEMBER(@.FromTimesheetDate, CONSTRAINED)
: STRTOMEMBER(@.ToTimesheetDate, CONSTRAINED) ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

So the moral of the story here is that I want to get
a list of all the projects, then all the users that have
charged time to each project. Then I want the total
hours for the time range specified AS WELL AS the
total time charge up until the cutoff date (@.ToTimesheetDate).

Now as I have just learned you cannot have aggregates
in your Measures, so can some kind soul please
point me to a source where I can ejumicate myself on
this MDX magic? It seems that its much more difficult
to operate with ranges of time in MDX than I though it should be.

|||If you are trying to do something like I
described above, here is how to do it:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
([Project].[Project Number].&[PA01203] * [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]} ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

|||If you are trying to do something like I
described above, here is how to do it:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
(
[Project].[Project Number].&[PA01203] *
[Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS
)
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]}
ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

Thursday, March 22, 2012

creating text file ?? ways

1. How can i create text file using SQL query and T SQL ?
2. How many ways are there to achieve this. ?
thnkz
--
Mahesh kumar.R~Maheshkumar.r wrote:
> 1. How can i create text file using SQL query and T SQL ?
> 2. How many ways are there to achieve this. ?
Read BOL article "Copying Data From a Query to a Data File" for more info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||http://www.aspfaq.com/2482
On 3/15/05 11:20 PM, in article evadE#dKFHA.1476@.TK2MSFTNGP09.phx.gbl,
"~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote:

> 1. How can i create text file using SQL query and T SQL ?
> 2. How many ways are there to achieve this. ?
> thnkz
> --
> Mahesh kumar.R
>|||What is that specific you would want on the text file? The data too ? or
query output? Can you elaborate. To list a few options, You can look into
Query Analyzer features of output to file. You can see bcp and osql commands
in conjuction with xp_cmdshell.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote in message
news:evadE%23dKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> 1. How can i create text file using SQL query and T SQL ?
> 2. How many ways are there to achieve this. ?
> thnkz
> --
> Mahesh kumar.R
>|||thnk, Yes i want to export table > text file and few query outputs to text
file ?
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:d18hgs$v68$1@.news01.intel.com...
> What is that specific you would want on the text file? The data too ? or
> query output? Can you elaborate. To list a few options, You can look into
> Query Analyzer features of output to file. You can see bcp and osql
commands
> in conjuction with xp_cmdshell.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote in message
> news:evadE%23dKFHA.1476@.TK2MSFTNGP09.phx.gbl...
>

Creating tables in SQL Server via query analyzer with relationships

I've been searching around for some info on how to set this up, but with no luck.
I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.
I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.
For instance, I can create the tables just fine....
CREATE TABLE students ( sId int NOT NULL PRIMARY KEY,
studentId varchar(50) NOT NULL,
course varchar(50)
)

CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY,
course varchar(50) NOT NULL,
sco varchar(50)
)
But, I need to set up relationships in there somehow.
Once student may have many courses (one to many) and one course may have many sco's (one to many)
SCO would be another table.
Can someone point me to a good link that would show how to complete these procedures?
Thanks all,
Zath

"sId"? "StudentId"?? "cId"?? Great start for a disaster of a system right there. If that's just a taste of your schema, you need to scrap it and start over. If you don't understand what's so wrong with it, spend a few hours reading about the relational model.
Anyway, you need to look in the BOL on DDL statements. Foreign keys are implemented via constraints:
CREATE TABLE Order_Details
(
Order_Num CHAR(12) NOT NULL
CONSTRAINT FK_Order_Detail_Orders
FOREIGN KEY (Order_Num)
REFERENCES Orders (Order_Num),
Prod_Num CHAR(8) NOT NULL
CONSTRAINT FK_Order_Detail_Products
FOREIGN KEY (Prod_Num)
REFERENCES Products (Prod_Num),
CONSTRAINT FK_Order_Details
PRIMARY KEY CLUSTERED (Order_Num, Prod_Num)
)

|||An easy way to do this, is set up your tables via enterprise manager, and then use All Tasks > Generate SQL Script. Inside the script generator, you can click on the Options tab, and click script primary keys, foreign keys, defaults and check contraints.
Nick|||Check out this T-SQL reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp

There are several ways to do it, but one of these should work

B. Use FOREIGN KEY constraints

A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. This example shows a single-column FOREIGN KEY constraint on the employee table that references the jobs table. Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.

job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)

You can also explicitly use the FOREIGN KEY clause and restate the column attribute. Note that the column name does not have to be the same in both tables.

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

Multicolumn key constraints are created as table constraints. In the pubs database, the sales table includes a multicolumn PRIMARY KEY. This example shows how to reference this key from another table; an explicit constraint name is optional.

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)|||Thanks everyone for their input. I have already gotten rid of the sID and so forth or rather didn't make them primary keys, just an autonumber.
It's been a while since I developed a database, I like to stick to code and need a database refresher it seems.
Once I redo this, if I have any other problems, I repost.
Thanks,
Zath

Tuesday, March 20, 2012

Creating table where column_names are results from query from other table

How I can create table where column name is result of query of second table?

Next cod doesnt work:

Use Pubs
declare @.naz varchar(40)
declare naziv cursor
for
select top 1 au_lname from authors

open naziv
FETCH NEXT FROM naziv INTO @.naz
close naziv
deallocate naziv
GO

CREATE TABLE #t1
(@.naz varchar(20),
quote int
)
GO

DROP TABLE #t1

use dynamic sql, although it won't work for temp tables.

EXECUTE('create table t1 (' + @.naz + ' varchar(20), quote int)'

Also, you don't need a cursor to do what you're doing above, it's overkill.

|||You don't need a cursor if you are only returning one row. Otherwise, you will need a cursor to exec the dynamic sql. If you just want the code to execute to create a number of tables, then you can do all the above in a stored procedure and have it output the code, without resorting to dynamic SQL (as you will be doing the executing). Thus:

declare @.dynsql varchar(100)
set @.dynsql = 'CREATE TABLE #t1 ( ' + @.naz + ' varchar(20), quote int ) GO'

sp_executesql @.dynsql

If you want to be really clever, and want to be able to dynamically set the type, size and precision of the column(s) to create, query INFORMATION_SCHEMA.COLUMNS for this information

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = @.naz

You will probably want to specify the tablename of the originating column that you are copying in the new table in the above query aswell.

HTH

For more SQL tips, check out my blog:
|||Use ALTER TABLE with dynamic SQL to add the columns. The base definition will be used in the CREATE TABLE statement.

Monday, March 19, 2012

Creating stored procedure

I have an inline sql query which i want to convert to a stored procedure.
The query is simplified as follows:

string sql = "SELECT * FROM property_property WHERE Location = " + location;
if(state != null) sql += " AND State = " + state;
if(cost !=null) sql += " AND Cost = " + cost;

The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc

I know some of the basics and managed to convert every other query except this.

Any help would be appreciatedcheck out BOL for sp_executesql.

hth|||Here is a way - probably not the most elegant but:

CREATE PROCEDURE MyStoredProcedure
@.Location NVARCHAR(50),
@.State NVARCHAR(50) = NULL,
@.Cost NVARCHAR(50) = NULL

DECLARE @.Query NVARCHAR(2000)
SET @.Query = 'SELECT * FROM property_property WHERE Location = ''' + @.Location + ''''

IF NOT @.State IS NULL
BEGIN
SET @.Query = @.Query + ' AND State = ''' + @.State + ''''
END

IF NOT @.Cost IS NULL
BEGIN
SET @.Query = @.Query + ' AND City = ''' + @.City + ''''
END

EXEC sp_executesql @.Query

For debugging purposes, you might replace the "EXEC sp_executesql @.Query" with "PRINT @.Query" just to make sure it is building the correct SQL statement.

Hope that helps,
Ian|||


CREATE PROCEDURE dbo.ReadMoreBooks
 @.State varchar(50), -- change datatypes to match sql's
 @.Cost varchar(50), -- change datatypes to match sql's
 @.Location varchar(50) -- change datatypes to match sql's
ASSELECT
 *, -- i hate using *. List all the fields
 StateSelect = (
 CASE
 WHEN @.State IS NOT NULL THEN
 @.State
 WHEN @.State IS NULL THEN
 Null
 END
 ),
 CostSelect = (
 CASE
 WHEN @.Cost IS NOT NULL THEN
 @.Cost
 WHEN @.Cost IS NULL THEN
 Null
 END
 )
FROM
 property_property -- weird table name, but ok.
WHERE
 Location = @.Location
AND StateSelect = @.State
AND CostSelect = @.Cost

That's one way. Didn't try, but maybe.

Sunday, March 11, 2012

Creating Scripts via Query Analyzer or Enterprise Manager

I'm trying to decide what is the best practice in terms of creating scripts
that, for instance, update an existing database. It seems there are a
couple of ways to do this (although I am sure there are more). It is often
suggested that you code your changes, by hand, using Enterprise Manager. As
I am looking at examples how to do this, I understand some of the basics of
doing this. For instance, if I have a tabled called Numbers and there is a
field named Employee (data type int, length 4, non nullable) that I want to
add a unique constraint to, it seems that most examples have the following
simplistic syntax:
ALTER TABLE Numbers
ADD CONSTRAINT EmployeeUnique
UNIQUE(Employee)
If I make the same change via Enterprise Manager and then click the Save
Change Script button, the amount of code it creates is much more:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Numbers ADD CONSTRAINT
IX_Numbers UNIQUE NONCLUSTERED
(
Employee
) ON [PRIMARY]
GO
COMMIT
So, is all of this extra code necessary? Are the examples I am looking at
too simplistic? Is it OK to use EM to make changes and then generate the
scripts?
Thanks.It si absolutely Ok to that in QA (the way you did).
All these SET statements, generated by EM, are actually happening whenever
you create a new session in QA, BUT behind the scene. You may see this if
you trace tsql exec from QA by Profiler.
With transactions is the same situation: in case of implicit transactions,
new T-SQL statement starts a new transaction. This explains Begin-commit
blocks inside EM-generated code.
All these settings are server defaults and can be changed.
Regards,
Marko Simic
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating script
s
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is ofte
n
> suggested that you code your changes, by hand, using Enterprise Manager.
As
> I am looking at examples how to do this, I understand some of the basics o
f
> doing this. For instance, if I have a tabled called Numbers and there is
a
> field named Employee (data type int, length 4, non nullable) that I want t
o
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>|||Do you save all your change scripts so you have a complete audit trail of al
l
changes made to your database? How you make the changes is just one part of
database change management. Check out this article on the subject
http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
This is an article showing an approach to database change managment that
will always work and gives you a complete audit trail on all database change
s
without slowing down the development of the database. It was the foundation
of the software called DB Ghost (http://www.dbghost.com)
How the database is developed (whether you use Query Analyzer, Enterprise
Manager or other third party tools) holds little relevance to the larger
problems of deploying those changes and having a clear picture of what has
changed, when it was changed, who it was changed by - so any problems can b
e
quickly solved in a repeatable and totally auditable manner. This (I believe
)
is the essence of database change management.
Excuse me for ranting, however I find it very frustrating watching the same
mistakes repeated time and again...
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating script
s
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is ofte
n
> suggested that you code your changes, by hand, using Enterprise Manager.
As
> I am looking at examples how to do this, I understand some of the basics o
f
> doing this. For instance, if I have a tabled called Numbers and there is
a
> field named Employee (data type int, length 4, non nullable) that I want t
o
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>

Creating Scripts via Query Analyzer or Enterprise Manager

I'm trying to decide what is the best practice in terms of creating scripts
that, for instance, update an existing database. It seems there are a
couple of ways to do this (although I am sure there are more). It is often
suggested that you code your changes, by hand, using Enterprise Manager. As
I am looking at examples how to do this, I understand some of the basics of
doing this. For instance, if I have a tabled called Numbers and there is a
field named Employee (data type int, length 4, non nullable) that I want to
add a unique constraint to, it seems that most examples have the following
simplistic syntax:
ALTER TABLE Numbers
ADD CONSTRAINT EmployeeUnique
UNIQUE(Employee)
If I make the same change via Enterprise Manager and then click the Save
Change Script button, the amount of code it creates is much more:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Numbers ADD CONSTRAINT
IX_Numbers UNIQUE NONCLUSTERED
(
Employee
) ON [PRIMARY]
GO
COMMIT
So, is all of this extra code necessary? Are the examples I am looking at
too simplistic? Is it OK to use EM to make changes and then generate the
scripts?
Thanks.
It si absolutely Ok to that in QA (the way you did).
All these SET statements, generated by EM, are actually happening whenever
you create a new session in QA, BUT behind the scene. You may see this if
you trace tsql exec from QA by Profiler.
With transactions is the same situation: in case of implicit transactions,
new T-SQL statement starts a new transaction. This explains Begin-commit
blocks inside EM-generated code.
All these settings are server defaults and can be changed.
Regards,
Marko Simic
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating scripts
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is often
> suggested that you code your changes, by hand, using Enterprise Manager. As
> I am looking at examples how to do this, I understand some of the basics of
> doing this. For instance, if I have a tabled called Numbers and there is a
> field named Employee (data type int, length 4, non nullable) that I want to
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>
|||Do you save all your change scripts so you have a complete audit trail of all
changes made to your database? How you make the changes is just one part of
database change management. Check out this article on the subject
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is an article showing an approach to database change managment that
will always work and gives you a complete audit trail on all database changes
without slowing down the development of the database. It was the foundation
of the software called DB Ghost (http://www.dbghost.com)
How the database is developed (whether you use Query Analyzer, Enterprise
Manager or other third party tools) holds little relevance to the larger
problems of deploying those changes and having a clear picture of what has
changed, when it was changed, who it was changed by - so any problems can be
quickly solved in a repeatable and totally auditable manner. This (I believe)
is the essence of database change management.
Excuse me for ranting, however I find it very frustrating watching the same
mistakes repeated time and again...
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating scripts
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is often
> suggested that you code your changes, by hand, using Enterprise Manager. As
> I am looking at examples how to do this, I understand some of the basics of
> doing this. For instance, if I have a tabled called Numbers and there is a
> field named Employee (data type int, length 4, non nullable) that I want to
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>

Creating Reports Using SQL QUERY ANALYZER

I am a student of the University of Phoenix. In My SQL class I was unsuccessful in creating a report using basic SQL commands using the SQL Query Analyzer version 8.00.760.

I am using a version of Microsoft SQL Server 2000 Sold to me by the college. The Disk 1 said SQL Server 2000 Developer Edition. Disk 2 said SQL Server 2000 service pack 3a. The Third Disk Says SQL Server 2000 Reporting Services.

Since I am learning SQL on this platform I wanted to create reports using the SQL Query Analyzer. So how do I create reports using Basic Commands. This is what I have so far.

CREATE TABLE ACCOUNTS
(
Account_Number INT NOT NULL PRIMARY KEY,
Long_Description VarChar(500) NOT NULL,
Short_Description VarChar(500)NOT NULL,
Balance Money NULL,
);

Once I Imported the data from an excel file. I created a view.

CREATE VIEW Account_Report
(Account, Descript, Identifier, Balance) AS
SELECT Account_Number, Long_Description, Short_Description, Balance
FROM Accounts

Then from this view I pulled my report, and the best I could come up with was

SELECT *
FROM Account_Report
ORDER BY Account COMPUTE SUM(Balance)

I want to do more. Such as Create headers, Justify Left right or center, FORMAT Money Column to only have 2 decimal places, Trim the extra space on the right side of the columns, rename the columns, and scroll down 20 lines at a time.

Any help would be appreciated. My class is over so this is realy all just for the furthering of my own knowlegde.

Noctechie

if you are up to making professional reports you have make use of the reporting services

now for your needs, you have to integrate function in your select statement

here some to start with

String Functions

The following table contains samples of string functions. For more information, see String Functions and Using String Functions.

Function

Description

Example

LCASE( )¹,
LOWER( )

Converts strings to lowercase

SELECT UPPER(substring(lname, 1, 1)) +

LOWER(substring (lname, 2, 99))

FROM employee

Displays a last name after the first character is converted to uppercase and the remaining characters to lowercase.

LTRIM( )

Removes leading spaces from a string

SELECT stor_name, LTRIM(stor_address)

FROM stores

Displays an address column after extraneous spaces are removed from the front.

SUBSTRING( )

Extracts one or more characters from a string

SELECT SUBSTRING(phone,1,3)

FROM employee

Displays the first three characters (the area code) of a phone number.

UCASE( )¹,
UPPER( )

Converts strings to uppercase

SELECT * FROM employee

WHERE UPPER(lname) = 'SMITH'

Converts the contents of the lname column to uppercase before comparing them to a specific value (avoids mismatches if the search is case sensitive). For details about case sensitivity in SQL Server, see Query Designer Considerations .

¹ If calling as an ODBC function, use syntax such as: { fn LCASE(text) }.

Date Functions

The following table contains samples of date functions. For more information, see Date and Time Functions.

Function

Description

Example

DATEDIFF( )

Calculates an interval between two dates.

SELECT fname, lname, hire_date

FROM employee

WHERE DATEDIFF(year, hire_date, getdate()) > 5

Locates all employees hired more than five years ago.

DATEPART( )

Returns the specified portion of a date or datetime column, including the day, month, or year.

SELECT DATEPART(year, hire_date)

FROM employee

Displays only the year in which an employee was hired (not the full date).

CURDATE( )¹,
GETDATE( ) or DATE( )

Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.

SELECT order_id

FROM orders

WHERE order_date = GETDATE()

Displays orders placed today.

¹ If calling as an ODBC function, use syntax such as: { fn CURDATE() }.

Mathematical Functions

The following functions are typical of those available in many databases. Refer to Mathematical Functions for more information.

Note You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.

Function

Description

Example

ROUND( )

Rounds a number off to the specified number of decimal places

SELECT ROUND(qty * (price * discount), 2)

FROM sales

Displays a total price based on a discount, then rounds the results off to two decimal places.

FLOOR( )

Rounds a number down to the nearest (smallest) whole number

UPDATE titles

SET price = FLOOR(price)

Rounds all prices in the titles table down to the nearest whole number.

CEILING( )

Rounds a number up to the nearest whole number

INSERT INTO archivetitle

SELECT title, CEILING(price)

FROM titles

Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.

System Functions

The following functions are typical of those available in many databases. For more information, see System Functions.

Function

Description

Example

DATALENGTH( )

Returns the number of bytes used by the specified expression

SELECT DATALENGTH(au_lname + ', '

+ au_fname)

FROM authors

Lists the number of bytes required for the combination of last and first names.

USER( )¹,
USER_NAME( )

Returns the current user name

SELECT company_name, city, phone

FROM customers

WHERE salesperson = USER_NAME()

Creates a list of customers for the salesperson who runs the query.

¹ If calling as an ODBC function, use syntax such as: { fn USER() }.

Other Functions

The following functions illustrate utility functions available in many databases. For more information, see Functions.

Function

Description

Example

CONVERT( )

Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.

SELECT 'Hired: ' + CONVERT(char (11),

hire_date)

FROM employee

Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.

SOUNDEX( )

Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.

SELECT au_lname, au_fname

FROM authors

WHERE SOUNDEX(au_fname) = 'M240'

Searches for names that sound like "Michael".

STR( )

Converts numeric data into a character string so you can manipulate it with text operators.

SELECT str(job_id) + ' ' +

str(job_lvl)

FROM employee

Displays the job_id and job_lvl columns (both numeric) in a single string.

Tuesday, March 27, 2012

Creating XML using For XML Explicit

Creating XML using For XML Explicit

Creating Views

Creating Views

Creating Views

Sunday, March 25, 2012

creating view as a parameter query

creating view as a parameter query

creating view as a parameter query

Creating View

Creating View

Creating View

Creating Total to-date query...

Thursday, March 22, 2012

creating text file ?? ways

Creating tables in SQL Server via query analyzer with relationships

Tuesday, March 20, 2012

Creating table where column_names are results from query from other table

Monday, March 19, 2012

Creating stored procedure

Sunday, March 11, 2012

Creating Scripts via Query Analyzer or Enterprise Manager

Creating Scripts via Query Analyzer or Enterprise Manager

Creating Reports Using SQL QUERY ANALYZER

String Functions

Date Functions

Mathematical Functions

System Functions

Other Functions

sql database integrity

Blog Archive

About Me