forcing data format mask without modifting code

I have statement which is comparing a smalldatetime column to literal string as follows:

sales_date ='21-9-2004 0:0:0.000'

when I run the statement in query analyzer it bombs out with:

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.

Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?There might be a global setting for how datetime fields are treated by default, I've never been tempted to go look for it. I'd rather do a CONVERT instead. There's also a 'SET DATEFORMAT' that might work for you.

What's wrong with changing the statement?|||Where is the data coming from? Is it always in that format? Can you use SUBSTRING?|||Unfortunately the data is in a liternal string 'DD-MM-YYYY' when in fact I require it be in 'MM-DD-YYYY' format.|||Couldn't you do something like...

cast(day(sales_date()) as varchar(2)) + '-' +
cast(month(sales_date()) as varchar(2)) + '-' +
cast(year(sales_date()) as char(4)) + ' 0:0:0.000'

??

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Force to Render to PDF

Hi
Is it possible to force a report to always render in a specific format (in
this case PDF) and remove the option for the user to change the format in
Report Manager
Thanks
PuddyUsing the standard front end, the answer is no...
You have the capability to modify XML configuration files to expose new
render formats, or to prevent the display and use of existing formats, but
this applies to all users..
To do a custom thing, you'd have to write the front using the API or URL
access with the render command...
You could also perhaps hide the report, then add an html page which simply
calls the URL passing the PDF renderer - That might work for you..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it'''s
community of SQL Professionals.
"Puddy" wrote:
> Hi
> Is it possible to force a report to always render in a specific format (in
> this case PDF) and remove the option for the user to change the format in
> Report Manager
> Thanks
> Puddysql

Force to date format MMDDYY

My sql :

SELECT....., date_ of_ shipment,.....

FROM......

WHERE.....

ORDER BY....

After that it will export to excel.

My problem: Some times I get a column of mixing date format MMDDYY and number.?

I have to format the whole excel column to get all date format.

My question: How can I write sql so it force the the result to be date format MMDDYY in excel? Where this clause in sql (where clause ?)

Thanks
Daniel

Code Snippet

select convert(varchar(8), getdate(), 1)

OR

select replace(convert(varchar(8), getdate(), 1), '/', '')

Depending on whether you want the slash separators or not.

Use code 101 instead of 1 in the Convert if you want 4 digit years.

|||

If my column name is date_of _shipment

Select convert (varchar(8), date_of_shipment,1) ?

Thnks

Daniel

|||

Right.

It will also lose it's name, so you may want to re-alias it.

Code Snippet

Select convert (varchar(8), date_of_shipment,1) AS date_of_shipment

|||

Thanks a millions. Look like it work great for what ever my excel column format.

Daniel

|||

I may need hour: min: second too

How about MMDDYYYYHHMMSS ?

Thanks
Daniel

force protocal encryption

This is a multi-part message in MIME format.
--=_NextPart_000_008F_01C503E2.52829930
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I could like to understand the SSL "force protocal encryption" on SQL = 2000, if I do this:
1: how much extra resource it will take to do encryption?
2: What certificate should we use, must we use certicate from public = certification authority?
3: how will the client side affected after I turn on force protocal = encryption? should it be transparent to them if we install public = certificate on the server?
4: Can client still connect to server using IP address, or must FQDN is = required?
Thanks
Frank
--=_NextPart_000_008F_01C503E2.52829930
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

I could like to understand the SSL = "force protocal encryption" on SQL 2000, if I do this:

1: how much extra resource it will take = to do encryption?
2: What certificate should we use, must = we use certicate from public certification authority?
3: how will the client side affected = after I turn on force protocal encryption? should it be transparent to them if we install public certificate on the server?
4: Can client still connect to server = using IP address, or must FQDN is required?

Thanks
Frank


--=_NextPart_000_008F_01C503E2.52829930--This is a multi-part message in MIME format.
--=_NextPart_000_009E_01C503EA.A15351F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I follow the MS link below and trying to Request New Certificate for SSL =using MMC, however, I cannot see "Request New Certificate" from the MMC =(under How to Configure the MMC Snap-in-->2-->b)
Is there other way to request a verisign certificate except from =certificate server?
Thanks
Frank
How to enable SSL encryption for SQL Server 2000 with Microsoft =Management Console
http://support.microsoft.com/kb/316898/EN-US/
"Frank" <signup0702@.sina.com> wrote in message =news:eEbJe85AFHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
I could like to understand the SSL "force protocal encryption" on SQL =2000, if I do this:
1: how much extra resource it will take to do encryption?
2: What certificate should we use, must we use certicate from public =certification authority?
3: how will the client side affected after I turn on force protocal =encryption? should it be transparent to them if we install public =certificate on the server?
4: Can client still connect to server using IP address, or must FQDN =is required?
Thanks
Frank
--=_NextPart_000_009E_01C503EA.A15351F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I follow the MS link below and trying to Request New Certificate =for SSL using MMC, however, I cannot see "Request New Certificate" from the MMC =(under How to Configure the MMC Snap-in-->2-->b)
Is there other way to request a =verisign certificate except from certificate server?
Thanks
Frank
How to enable SSL encryption for SQL Server 2000 =with Microsoft Management Console
http://support.microsoft.com/kb/316898/EN-US/">http://support.mic=rosoft.com/kb/316898/EN-US/
"Frank" wrote =in message news:eEbJe85AFHA.1400=@.TK2MSFTNGP11.phx.gbl...
Hi,

I could like to understand the SSL ="force protocal encryption" on SQL 2000, if I do this:

1: how much extra resource it will =take to do encryption?
2: What certificate should we use, =must we use certicate from public certification authority?
3: how will the client side affected =after I turn on force protocal encryption? should it be transparent to them if we install public certificate on the server?
4: Can client still connect to server =using IP address, or must FQDN is required?

Thanks
Frank

---

--=_NextPart_000_009E_01C503EA.A15351F0--

For XML Path problems

Can anyone tell me how to add multiple columns with the same name? Here is an example of the XML format I'm trying to create using For XML Path

<TABLETYPE TYPEABBRV=“IDEADISAB” TOTALINDICATOR=“N”>
<CATEGORY TYPE=“DISABCATIDEA” VALUE=“AUT”/>
<CATEGORY TYPE=“AGESA” VALUE=“6”/>
<CATEGORY TYPE=“EDENVIRIDEASA” VALUE=“RC80”/>
<AMOUNT>10</AMOUNT>
</TABLETYPE>

Here is the query I was trying to use

SELECT

'IDEADISAB' AS '@.TYPEABBRV',

'N' AS '@.TOTALINDICATOR',

'DISABCATIDEA' AS 'CATEGORY/@.TYPE',

IdeaCategory AS 'CATEGORY/@.VALUE',

'AGESA' AS 'CATEGORY/@.TYPE',

AGE AS 'CATEGORY/@.VALUE',

'EDENVIRIDESAS' AS 'CATEGORY/@.TYPE',

EECATEGORY AS 'CATEGORY/@.VALUE',

COUNT(*) AS 'AMOUNT'

FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory

FOR XML PATH('TABLETYPE'), TYPE)

And this is the error I'm getting

Msg 6810, Level 16, State 1, Line 1

Column name 'CATEGORY/@.TYPE' is repeated. The same attribute cannot be generated more than once on the same XML tag.

Any help would be much appreciated

Use subqueries

SELECT
'IDEADISAB' AS '@.TYPEABBRV',
'N' AS '@.TOTALINDICATOR',
(SELECT
'DISABCATIDEA' AS '@.TYPE',
IdeaCategory AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'AGESA' AS '@.TYPE',
AGE AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'EDENVIRIDESAS' AS '@.TYPE',
EECATEGORY AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
COUNT(*) AS 'AMOUNT'
FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory
FOR XML PATH('TABLETYPE'), TYPE

|||

Thanks Mark,

I had already tried using subqueries but could not quite get the syntax correct. I was using the 'from' statement after every select and that was throwing all of my records under one tag. Thanks again.

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML EXPLICIT - Empty Tags?

Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
...
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
...
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstate as [state!1!idstate],
null as [property!2!name!element],
null as [property!2!areaHA!element],
null as [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty
]
for xml explicit
Cheers,
AndrewYou can do it by adding another tag (and therefore another UNION) to your
query in which the empty "container" element is craeted by selecting NULL as
shown in the following example from Northwind.
An alternative approach would be to use an annotated schema with a
sql:is-constant annotation.
cheers,
Graeme
sample code
--
Use Northwind
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Invoices!1],
NULL AS [Invoice!2!InvoiceNo],
NULL AS [Invoice!2!Date],
NULL AS [Item!3!Product],
NULL AS [Item!3!Price!element]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
OrderID,
OrderDate,
NULL,
NULL
FROM Orders
UNION ALL
SELECT 3,
2,
NULL,
O.OrderID,
NULL,
P.ProductName,
OD.UnitPrice
FROM Orders O JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
JOIN Products P
ON OD.ProductID = P.ProductID
ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
FOR XML EXPLICIT
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Bunce" <Bunce@.discussions.microsoft.com> wrote in message
news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
...
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
...
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstate as [state!1!idstate],
null as [property!2!name!element],
null as [property!2!areaHA!element],
null as [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty
]
for xml explicit
Cheers,
Andrew|||Note that this becomes much easier in SQL Server 2005 (see
http://msdn.microsoft.com/library/e...l/forxml2k5.asp). Note
that I find such wrapper elements to be of questionable value when they only
provide an additional level of indirection in your tree. Since XPath has
list semantics, /Invoice already gives you all invoices. If you add
additional properties to Invoices such as summary information, it makes
sense.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OQmoBJtlFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can do it by adding another tag (and therefore another UNION) to your
> query in which the empty "container" element is craeted by selecting NULL
> as
> shown in the following example from Northwind.
> An alternative approach would be to use an annotated schema with a
> sql:is-constant annotation.
> cheers,
> Graeme
> sample code
> --
> Use Northwind
> SELECT 1 AS Tag,
> NULL AS Parent,
> NULL AS [Invoices!1],
> NULL AS [Invoice!2!InvoiceNo],
> NULL AS [Invoice!2!Date],
> NULL AS [Item!3!Product],
> NULL AS [Item!3!Price!element]
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> NULL,
> OrderID,
> OrderDate,
> NULL,
> NULL
> FROM Orders
> UNION ALL
> SELECT 3,
> 2,
> NULL,
> O.OrderID,
> NULL,
> P.ProductName,
> OD.UnitPrice
> FROM Orders O JOIN [Order Details] OD
> ON O.OrderID = OD.OrderID
> JOIN Products P
> ON OD.ProductID = P.ProductID
> ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
> FOR XML EXPLICIT
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Bunce" <Bunce@.discussions.microsoft.com> wrote in message
> news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
> Hey all.
> I need to output a query in a given XML format and I figure I'd have a
> stab
> using the FOR XML clause of SQL 2000 first rather than in code.
> I've worked out the basics using the FOR XML EXPLICIT clause and am able
> to
> output a structure such as this:
> <state id="SA">
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </state>
> <state id="NSW">
> <property id="24">
> ...
> </state>
>
> However I haven't worked out how to add 'empty' surrounding tags (I think
> they may be called 'associations' in XML speak), so the schema would
> become:
> (note the addition of the <states> and <properties> tags)
> <states>
> <state id="SA">
> <properties>
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </properties>
> </state>
> <state id="NSW">
> <properties>
> <property id="24">
> ...
> </properties>
> </state>
> </states>
> (apologies if the formatting doesn't stick)
> Any ideas? Anyone familiar with using XML in SS?
> For reference, my actual current query is below - I figured the above
> example was easier to use.
> SELECT
> 1 as Tag,
> NULL as Parent,
> c.textstate as [state!1!idstate],
> null as [property!2!name!element],
> null as [property!2!areaHA!element],
> null as [property!2!dateGranted!element],
> null as
> [property!2!titleHoldingBody!element],
> null as [property!2!idproperty]
> FROM
> dbo.tblStates c
> Where
> c.IDState<>0
> union all
> SELECT
> 2,
> 1,
> b.textstate,
> a.ShortLandName,
> a.area,
> a.GrantDate,
> a.THBName,
> a.IDProperty
> FROM
> dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
> a.IDState = b.IDState
> Order By
> [state!1!idstate],[property!2!idproperty
]
> for xml explicit
> Cheers,
> Andrew
>

FOR XML EXPLICIT - Empty Tags?

Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
...
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
...
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstateas [state!1!idstate],
nullas [property!2!name!element],
nullas [property!2!areaHA!element],
nullas [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty]
for xml explicit
Cheers,
Andrew
You can do it by adding another tag (and therefore another UNION) to your
query in which the empty "container" element is craeted by selecting NULL as
shown in the following example from Northwind.
An alternative approach would be to use an annotated schema with a
sql:is-constant annotation.
cheers,
Graeme
sample code
Use Northwind
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Invoices!1],
NULL AS [Invoice!2!InvoiceNo],
NULL AS [Invoice!2!Date],
NULL AS [Item!3!Product],
NULL AS [Item!3!Price!element]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
OrderID,
OrderDate,
NULL,
NULL
FROM Orders
UNION ALL
SELECT 3,
2,
NULL,
O.OrderID,
NULL,
P.ProductName,
OD.UnitPrice
FROM Orders O JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
JOIN Products P
ON OD.ProductID = P.ProductID
ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
FOR XML EXPLICIT
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Bunce" <Bunce@.discussions.microsoft.com> wrote in message
news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
Hey all.
I need to output a query in a given XML format and I figure I'd have a stab
using the FOR XML clause of SQL 2000 first rather than in code.
I've worked out the basics using the FOR XML EXPLICIT clause and am able to
output a structure such as this:
<state id="SA">
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</state>
<state id="NSW">
<property id="24">
....
</state>
However I haven't worked out how to add 'empty' surrounding tags (I think
they may be called 'associations' in XML speak), so the schema would become:
(note the addition of the <states> and <properties> tags)
<states>
<state id="SA">
<properties>
<property id="5">
<name>Prop1</name>
<area>35</area>
</property>
<property id="10">
<name>Prop2</name>
<area>55</area>
</property>
</properties>
</state>
<state id="NSW">
<properties>
<property id="24">
....
</properties>
</state>
</states>
(apologies if the formatting doesn't stick)
Any ideas? Anyone familiar with using XML in SS?
For reference, my actual current query is below - I figured the above
example was easier to use.
SELECT
1 as Tag,
NULL as Parent,
c.textstate as [state!1!idstate],
null as [property!2!name!element],
null as [property!2!areaHA!element],
null as [property!2!dateGranted!element],
null as
[property!2!titleHoldingBody!element],
null as [property!2!idproperty]
FROM
dbo.tblStates c
Where
c.IDState<>0
union all
SELECT
2,
1,
b.textstate,
a.ShortLandName,
a.area,
a.GrantDate,
a.THBName,
a.IDProperty
FROM
dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
a.IDState = b.IDState
Order By
[state!1!idstate],[property!2!idproperty]
for xml explicit
Cheers,
Andrew
|||Note that this becomes much easier in SQL Server 2005 (see
http://msdn.microsoft.com/library/en.../forxml2k5.asp). Note
that I find such wrapper elements to be of questionable value when they only
provide an additional level of indirection in your tree. Since XPath has
list semantics, /Invoice already gives you all invoices. If you add
additional properties to Invoices such as summary information, it makes
sense.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OQmoBJtlFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can do it by adding another tag (and therefore another UNION) to your
> query in which the empty "container" element is craeted by selecting NULL
> as
> shown in the following example from Northwind.
> An alternative approach would be to use an annotated schema with a
> sql:is-constant annotation.
> cheers,
> Graeme
> sample code
> --
> Use Northwind
> SELECT 1 AS Tag,
> NULL AS Parent,
> NULL AS [Invoices!1],
> NULL AS [Invoice!2!InvoiceNo],
> NULL AS [Invoice!2!Date],
> NULL AS [Item!3!Product],
> NULL AS [Item!3!Price!element]
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> NULL,
> OrderID,
> OrderDate,
> NULL,
> NULL
> FROM Orders
> UNION ALL
> SELECT 3,
> 2,
> NULL,
> O.OrderID,
> NULL,
> P.ProductName,
> OD.UnitPrice
> FROM Orders O JOIN [Order Details] OD
> ON O.OrderID = OD.OrderID
> JOIN Products P
> ON OD.ProductID = P.ProductID
> ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
> FOR XML EXPLICIT
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Bunce" <Bunce@.discussions.microsoft.com> wrote in message
> news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
> Hey all.
> I need to output a query in a given XML format and I figure I'd have a
> stab
> using the FOR XML clause of SQL 2000 first rather than in code.
> I've worked out the basics using the FOR XML EXPLICIT clause and am able
> to
> output a structure such as this:
> <state id="SA">
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </state>
> <state id="NSW">
> <property id="24">
> ...
> </state>
>
> However I haven't worked out how to add 'empty' surrounding tags (I think
> they may be called 'associations' in XML speak), so the schema would
> become:
> (note the addition of the <states> and <properties> tags)
> <states>
> <state id="SA">
> <properties>
> <property id="5">
> <name>Prop1</name>
> <area>35</area>
> </property>
> <property id="10">
> <name>Prop2</name>
> <area>55</area>
> </property>
> </properties>
> </state>
> <state id="NSW">
> <properties>
> <property id="24">
> ...
> </properties>
> </state>
> </states>
> (apologies if the formatting doesn't stick)
> Any ideas? Anyone familiar with using XML in SS?
> For reference, my actual current query is below - I figured the above
> example was easier to use.
> SELECT
> 1 as Tag,
> NULL as Parent,
> c.textstate as [state!1!idstate],
> null as [property!2!name!element],
> null as [property!2!areaHA!element],
> null as [property!2!dateGranted!element],
> null as
> [property!2!titleHoldingBody!element],
> null as [property!2!idproperty]
> FROM
> dbo.tblStates c
> Where
> c.IDState<>0
> union all
> SELECT
> 2,
> 1,
> b.textstate,
> a.ShortLandName,
> a.area,
> a.GrantDate,
> a.THBName,
> a.IDProperty
> FROM
> dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a where
> a.IDState = b.IDState
> Order By
> [state!1!idstate],[property!2!idproperty]
> for xml explicit
> Cheers,
> Andrew
>

For XML Explicit

friends,
Below is the table "PCreditCardType"
I wanted to change the data of that table to the below mentioned XML format.
.
The solution should be through For XML Explicit or any SQL Operation...
Can you plz help me out'
Select * from PCreditCardType
CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
1 AMX American Express NULL
2 BCD BankCard NULL
3 DCB Diners Club NULL
4 DSC Discover NULL
5 ERC Eurocard NULL
6 JCB JCB NULL
7 MCD MasterCard NULL
8 VSA Visa NULL
<domainData name=" PCreditCardType " >
<cols>
<col name="CCTypeCode"/>
<col name="CCTypeName">
<col name="CCExpNumberOfYears">
</cols>
<rows>
<row> <![CDATA[AMX,American Express,NULL]]> </row>
<row> <![CDATA[BCD,BankCard,NULL]]> </row>
<row> <![CDATA[DCB,Diners Club,NULL]]> </row>
<row> <![CDATA[DSC,Discover,NULL]]> </row>
<row> <![CDATA[ERC,Eurocard,NULL]]> </row>
<row> <![CDATA[JCB,JCB,NULL]]> </row>
<row> <![CDATA[MCD,MasterCard,NULL]]> </row>
<row> <![CDATA[VSA,Visa,NULL]]> </row>
</rows>
</domainData>
Regards
Rao KRMYou can use the following query to dump XML rows section:
SELECT 1 as Tag,
NULL as Parent,
ISNULL(CCTypeCode, 'NULL') + ',' + ISNULL(CCTypeName, 'NULL') + ',' +
ISNULL(CCExpNumberOfYears, 'NULL') as [row!1!!cdata]
FROM PCreditCardType
FOR XML EXPLICIT
Then merge it with columns XML data in the application.
With best regards
Martin Rakhmanov
"For XML Explicit help wanted" <For XML Explicit help
wanted@.discussions.microsoft.com> wrote in message
news:73FCEF35-0CAE-4E51-B6DE-9A815BAF9DBA@.microsoft.com...
> friends,
> Below is the table "PCreditCardType"
> I wanted to change the data of that table to the below mentioned XML
format..
> The solution should be through For XML Explicit or any SQL Operation...
> Can you plz help me out'
> Select * from PCreditCardType
> CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
> 1 AMX American Express NULL
> 2 BCD BankCard NULL
> 3 DCB Diners Club NULL
> 4 DSC Discover NULL
> 5 ERC Eurocard NULL
> 6 JCB JCB NULL
> 7 MCD MasterCard NULL
> 8 VSA Visa NULL
>
> <domainData name=" PCreditCardType " >
> <cols>
> <col name="CCTypeCode"/>
> <col name="CCTypeName">
> <col name="CCExpNumberOfYears">
> </cols>
> <rows>
> <row> <![CDATA[AMX,American Express,NULL]]> </row>
> <row> <![CDATA[BCD,BankCard,NULL]]> </row>
> <row> <![CDATA[DCB,Diners Club,NULL]]> </row>
> <row> <![CDATA[DSC,Discover,NULL]]> </row>
> <row> <![CDATA[ERC,Eurocard,NULL]]> </row>
> <row> <![CDATA[JCB,JCB,NULL]]> </row>
> <row> <![CDATA[MCD,MasterCard,NULL]]> </row>
> <row> <![CDATA[VSA,Visa,NULL]]> </row>
> </rows>
> </domainData>
>
> Regards
> Rao KRM

For XML Explicit

friends,
Below is the table "PCreditCardType"
I wanted to change the data of that table to the below mentioned XML format..
The solution should be through For XML Explicit or any SQL Operation...
Can you plz help me out?
Select * from PCreditCardType
CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
1 AMX American Express NULL
2 BCD BankCard NULL
3 DCB Diners Club NULL
4 DSC Discover NULL
5 ERC Eurocard NULL
6 JCB JCB NULL
7 MCD MasterCard NULL
8 VSA Visa NULL
<domainData name=" PCreditCardType " >
<cols>
<col name="CCTypeCode"/>
<col name="CCTypeName">
<col name="CCExpNumberOfYears">
</cols>
<rows>
<row> <![CDATA[AMX,American Express,NULL]]> </row>
<row> <![CDATA[BCD,BankCard,NULL]]> </row>
<row> <![CDATA[DCB,Diners Club,NULL]]> </row>
<row> <![CDATA[DSC,Discover,NULL]]> </row>
<row> <![CDATA[ERC,Eurocard,NULL]]> </row>
<row> <![CDATA[JCB,JCB,NULL]]> </row>
<row> <![CDATA[MCD,MasterCard,NULL]]> </row>
<row> <![CDATA[VSA,Visa,NULL]]> </row>
</rows>
</domainData>
Regards
Rao KRM
You can use the following query to dump XML rows section:
SELECT 1 as Tag,
NULL as Parent,
ISNULL(CCTypeCode, 'NULL') + ',' + ISNULL(CCTypeName, 'NULL') + ',' +
ISNULL(CCExpNumberOfYears, 'NULL') as [row!1!!cdata]
FROM PCreditCardType
FOR XML EXPLICIT
Then merge it with columns XML data in the application.
With best regards
Martin Rakhmanov
"For XML Explicit help wanted" <For XML Explicit help
wanted@.discussions.microsoft.com> wrote in message
news:73FCEF35-0CAE-4E51-B6DE-9A815BAF9DBA@.microsoft.com...
> friends,
> Below is the table "PCreditCardType"
> I wanted to change the data of that table to the below mentioned XML
format..
> The solution should be through For XML Explicit or any SQL Operation...
> Can you plz help me out?
> Select * from PCreditCardType
> CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
> 1 AMX American Express NULL
> 2 BCD BankCard NULL
> 3 DCB Diners Club NULL
> 4 DSC Discover NULL
> 5 ERC Eurocard NULL
> 6 JCB JCB NULL
> 7 MCD MasterCard NULL
> 8 VSA Visa NULL
>
> <domainData name=" PCreditCardType " >
> <cols>
> <col name="CCTypeCode"/>
> <col name="CCTypeName">
> <col name="CCExpNumberOfYears">
> </cols>
> <rows>
> <row> <![CDATA[AMX,American Express,NULL]]> </row>
> <row> <![CDATA[BCD,BankCard,NULL]]> </row>
> <row> <![CDATA[DCB,Diners Club,NULL]]> </row>
> <row> <![CDATA[DSC,Discover,NULL]]> </row>
> <row> <![CDATA[ERC,Eurocard,NULL]]> </row>
> <row> <![CDATA[JCB,JCB,NULL]]> </row>
> <row> <![CDATA[MCD,MasterCard,NULL]]> </row>
> <row> <![CDATA[VSA,Visa,NULL]]> </row>
> </rows>
> </domainData>
>
> Regards
> Rao KRM

FOR XML AUTO, ELEMENTS

Hi,
Is it possible to include an attribute within the dataset returned using FOR
XML AUTO, ELEMENTS ?
The format returned when using ELEMENTS is perfect for what I need but I
just need to make the ID field an attribute rather than an element.
Grateful for any advice.
Alex
I am not an expert, but according to the docs you cannot do that with
AUTO. You will need to do it with RAW.
|||Actually, RAW (in SQL Server 2000) will return everything as attributes, and
as I understand it you want a mix of attributes and elements. To do that
you'd need to use EXPLICIT mode, as in this example:
USE Northwind
SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Item!1!ProductID],
ProductName AS [Item!1!Name!element],
UnitPrice AS [Item!1!Price!element]
FROM [Products]
FOR XML EXPLICIT
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"ali" <ali.jan@.gmail.com> wrote in message
news:1121413418.618949.238900@.g14g2000cwa.googlegr oups.com...
I am not an expert, but according to the docs you cannot do that with
AUTO. You will need to do it with RAW.
|||NB: I'm assuming you're using SQL Server 2000 - in SQL Server 2005 you could
use PATH mode.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:O81hWwRiFHA.3544@.TK2MSFTNGP15.phx.gbl...
Actually, RAW (in SQL Server 2000) will return everything as attributes, and
as I understand it you want a mix of attributes and elements. To do that
you'd need to use EXPLICIT mode, as in this example:
USE Northwind
SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Item!1!ProductID],
ProductName AS [Item!1!Name!element],
UnitPrice AS [Item!1!Price!element]
FROM [Products]
FOR XML EXPLICIT
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"ali" <ali.jan@.gmail.com> wrote in message
news:1121413418.618949.238900@.g14g2000cwa.googlegr oups.com...
I am not an expert, but according to the docs you cannot do that with
AUTO. You will need to do it with RAW.

FOR SQL XML format

When I run the following code:
SELECT CompanyName, ProductName, UnitsInStock, UnitsOnOrder
from Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID FOR XML auto
I get a continuous string that represents the xml results like this.
<Suppliers CompanyName="Exotic Liquids"><Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/><Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/><Products ProductName="Aniseed Syrup" UnitsInStock="13" UnitsOnOrder="70"/></Suppliers><Suppliers CompanyName="New Orleans Cajun Delights"><Products ProductName="Chef Anton's Cajun Seasoning" UnitsInStock="53" UnitsOnOrder="0"/><Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0" UnitsOnOrder="0"/></Suppliers><Suppliers CompanyName="Grandma Kelly's Homestead"><Products ProductName="Grandma's BoysenberrySpread" UnitsInStock="120" UnitsOnOrder="0"/><Products ProductName="Uncle Bob's Organic Dried Pears" UnitsInStock="15" UnitsOnOrder="0"/><Products ProductName="Northwoods Cranberry Sauce" UnitsInStock="6" UnitsOnOrder="0"/></Suppliers...
how do i get the reslut in the XML format like this with the indents and new lines(returns) after the xml objects instead of a single string?
<Suppliers CompanyName="Exotic Liquids">
<Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/>
<Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/>
<Products ProductName="Aniseed Syrup" UnitsInStock="13" UnitsOnOrder="70"/>
</Suppliers>
<Suppliers CompanyName="New Orleans Cajun Delights">
<Products ProductName="Chef Anton's Cajun Seasoning" UnitsInStock="53" UnitsOnOrder="0"/>
<Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0" UnitsOnOrder="0"/>
</Suppliers>...
I see all the examples like SQL parses out and format the xml in the results window
Thx
Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451
XML doesn't actually include indents and newlines - that's just a convenient
way to format it so we can read it more easily. Query Analyzer just displays
the XML stream that gets returned by the query - from an XML-aware client
application's point of view, this is fine; it's just not very
human-friendly! If you want to see it formatted you'd have to load it into
an XML editor that formats it for you (and bear in mind that FOR XML queries
return XML Fragments - not full documents, so there's no root element).
The good news is that the SQL Management Studio tool in SQL Server 2005
includes an XML editor, so you get the results of FOR XML queries nicely
formatted.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ron Sissons" <RSISSONS@.rcoe.k12.ca.us> wrote in message
news:%23h9ImsJhEHA.396@.TK2MSFTNGP12.phx.gbl...
When I run the following code:
SELECT CompanyName, ProductName, UnitsInStock, UnitsOnOrder
from Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
FOR XML auto
I get a continuous string that represents the xml results like this.
<Suppliers CompanyName="Exotic Liquids"><Products ProductName="Chai"
UnitsInStock="39" UnitsOnOrder="0"/><Products ProductName="Chang"
UnitsInStock="17" UnitsOnOrder="40"/><Products ProductName="Aniseed Syrup"
UnitsInStock="13" UnitsOnOrder="70"/></Suppliers><Suppliers CompanyName="New
Orleans Cajun Delights"><Products ProductName="Chef Anton's Cajun
Seasoning" UnitsInStock="53" UnitsOnOrder="0"/><Products ProductName="Chef
Anton's Gumbo Mix" UnitsInStock="0"
UnitsOnOrder="0"/></Suppliers><Suppliers CompanyName="Grandma Kelly's
Homestead"><Products ProductName="Grandma's Boysenberry Spread"
UnitsInStock="120" UnitsOnOrder="0"/><Products ProductName="Uncle Bob's
Organic Dried Pears" UnitsInStock="15" UnitsOnOrder="0"/><Products
ProductName="Northwoods Cranberry Sauce" UnitsInStock="6"
UnitsOnOrder="0"/></Suppliers...
how do i get the reslut in the XML format like this with the indents and new
lines(returns) after the xml objects instead of a single string?
<Suppliers CompanyName="Exotic Liquids">
<Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/>
<Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/>
<Products ProductName="Aniseed Syrup" UnitsInStock="13"
UnitsOnOrder="70"/>
</Suppliers>
<Suppliers CompanyName="New Orleans Cajun Delights">
<Products ProductName="Chef Anton's Cajun Seasoning"
UnitsInStock="53" UnitsOnOrder="0"/>
<Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0"
UnitsOnOrder="0"/>
</Suppliers>...
I see all the examples like SQL parses out and format the xml in the results
window
Thx
Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451
|||Thank you very much!
I thought I was going crazy not understanding..in all the examples the wording is:
"results in the following format:
<Shippers>
<ShipperID>1</ShipperID>
<CompanyName>Speedy Express</CompanyName>
<Orders>
<OrderID>10249</OrderID>
..."
I thought I was missing something.
Ron Sissons, DBAInformation Technology ServicesRiverside County Office of Education3939 Thirteenth Street, Riverside, CA 92502-0868Telephone: (909) 826-6471; FAX: [909] 826-6451