creating a standard template for dev team

I want to create a standard look and feel for my reports. I can do this from
the style perspective by editing the xml file.
But how can I create a template report that always has headers and footers
and has standard contents within them such as print date\time, page count
and a logo ?
Is there an external way of doing this or will it always have to be done in
code so that when a report is being previwed or printed, I edit the report
and add the appropriate code ?
thanks
BObHi Bob.
What I ended up doing was using the designer to build my standard look and
feel (one for portrait, one for landscape and one for charts), then save
them off to the Report Designer templates folder.
HTH
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"BOb Pardoe hayslogistics.co.uk>" <bob.pardoe@.<RemoveAntiSpam> wrote in
message news:OiwzVc0tEHA.2300@.TK2MSFTNGP09.phx.gbl...
> I want to create a standard look and feel for my reports. I can do this
from
> the style perspective by editing the xml file.
> But how can I create a template report that always has headers and footers
> and has standard contents within them such as print date\time, page count
> and a logo ?
> Is there an external way of doing this or will it always have to be done
in
> code so that when a report is being previwed or printed, I edit the report
> and add the appropriate code ?
> thanks
> BOb
>

Create XML tags from data in a 'FOR XML' query?

I am writing a long stored procedure to produce an XML output from a
relational database. For the most part (with help from these forums), it's
going well.
New question: I have a table we'll call 'Facets'. A simplified subset of
its columns include
facet_guid
facet_id
facet_type
facet_value
Is there a way to configure the query to create a set of elements from this
table using "facet_id" as the element name and "facet_value" as the value of
the element?
If not, I'm faced with the prospect of creating a function to pivot the
table, which would cause further problems in that I have a lower-level table
that I need to associate with each facet (via sub-query) using the facet_gui
d.
Any assistance would be appreciated. Thanks!Hello Grelan,
The best way to do this would be build up the command as a string (in T-SQL
or otherwise), the use sp_executesql to get the job done. Where you'll first
run into problems is that column names can't be resolved from a variable.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Create XML tags from data in a 'FOR XML' query?

I am writing a long stored procedure to produce an XML output from a
relational database. For the most part (with help from these forums), it's
going well.
New question: I have a table we'll call 'Facets'. A simplified subset of
its columns include
facet_guid
facet_id
facet_type
facet_value
Is there a way to configure the query to create a set of elements from this
table using "facet_id" as the element name and "facet_value" as the value of
the element?
If not, I'm faced with the prospect of creating a function to pivot the
table, which would cause further problems in that I have a lower-level table
that I need to associate with each facet (via sub-query) using the facet_guid.
Any assistance would be appreciated. Thanks!
Hello Grelan,
The best way to do this would be build up the command as a string (in T-SQL
or otherwise), the use sp_executesql to get the job done. Where you'll first
run into problems is that column names can't be resolved from a variable.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

CREATE XML SCHEMA COLLECTION ddl statement?

what's the purpose of defining a "relational schema name" in the CREATE XML SCHEMA COLLECTION ddl statement? i don't understand why it's an option.

All objects in SQL Server 2005 database distributed among schemas.

Default schema is dbo. So when you create table "MyTable", its full name dbo.MyTable or Sales.MyTable etc. For more examples, please, see database AdventureWorks.

XML SCHEMA COLLECTION also included into one of database schemas.

|||

Konstantin Kosinsky wrote:

All objects in SQL Server 2005 database distributed among schemas.

Thanks for your reply, but I already knew this. What I previously didn't understand was the purpose of schemas. Anyway, I found the following in the BOL:

"The roles of schemas are enhanced in SQL Server databases. All objects in a database are now located in schemas and are not owned by individual users. Each schema can be owned by roles, allowing multiple users to administer database objects. In earlier versions of SQL Server, users could not be dropped from a database without having to reassign the ownership of each individual object that they owned. Now ownership only has to be adjusted for the schema, not each object." http://msdn2.microsoft.com/en-us/library/ms170800.aspx

Aha! Now it all makes sense to me.

create xml from sql query

Hi I have a "configuration" table in a SQL database it has the parentid and itemid to create a tree, I need to create another tree froma an xml but i dont know how to relate them in the xml file , because when i create the xmlfile all the items are alike, any hints?

Thanks and regards.

To get started you can look at the different FOR XML modes in SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

FOR XML PATH is generally the one that give the most power with the least complexity:
http://msdn2.microsoft.com/en-us/library/ms189885.aspx

For more specific assistance, give us a little more context, about how your data is stored and how your rows are related to one another.

|||

The table has the tree structure, id, parent id, name where the parent id marks the realtion between the childs, when the parent id is null, it means that this is a parent node. I need to export it to an xml because one of my controls do not accept sql databinding only xml files.

Thanks and best regards.

|||

If you have a recursive structure like this, you can also investigate using CTE's or "Common Table Expressions" to generate recursive structures. Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You would want to write yours so that at each level, each nodes' child XML is nested within it.

|||

Great. I got resultset of parent child relationship using CTE . Now I want xml output as below

<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>

Can you suggest idle way for same?

|||As posted earlier, now use the FOR XML to return the result formatted as XML. If you can, post your CTE and we can show you how to modify that to return the xml you desire.|||

Here is query

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)

AS

(

SELECT

ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path

FROM

HumanResources.Employee

WHERE

ManagerID IS NULL

UNION ALL

SELECT

e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))

FROM

HumanResources.Employee AS e

JOIN

DirectReports AS d

ON

e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

ORDER BY Path

Hey, Thanks for your reply.

|||

Try something like this:

WITH DirectReports (ManagerID, EmployeeID, Title, Level, Path)

AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))

FROM HumanResources.Employee AS e

JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

ORDER BY Path

FOR XML RAW, ROOT('Emp')

I think that is what you are looking for, but if not your best bet is to play with the FOR XML clause. I will also keep playing with it as well to make sure.

Scott

create xml from sql query

Hi I have a "configuration" table in a SQL database it has the parentid and itemid to create a tree, I need to create another tree froma an xml but i dont know how to relate them in the xml file , because when i create the xmlfile all the items are alike, any hints?

Thanks and regards.

To get started you can look at the different FOR XML modes in SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

FOR XML PATH is generally the one that give the most power with the least complexity:
http://msdn2.microsoft.com/en-us/library/ms189885.aspx

For more specific assistance, give us a little more context, about how your data is stored and how your rows are related to one another.

|||

The table has the tree structure, id, parent id, name where the parent id marks the realtion between the childs, when the parent id is null, it means that this is a parent node. I need to export it to an xml because one of my controls do not accept sql databinding only xml files.

Thanks and best regards.

|||

If you have a recursive structure like this, you can also investigate using CTE's or "Common Table Expressions" to generate recursive structures. Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You would want to write yours so that at each level, each nodes' child XML is nested within it.

|||

Great. I got resultset of parent child relationship using CTE . Now I want xml output as below

<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>

Can you suggest idle way for same?

|||As posted earlier, now use the FOR XML to return the result formatted as XML. If you can, post your CTE and we can show you how to modify that to return the xml you desire.|||

Here is query

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID,Level,Path)

AS

(

SELECT

ManagerID, EmployeeID, Title, DepartmentID, 0 ASLevel,CAST(EmployeeID ASVARCHAR(MAX))ASPath

FROM

HumanResources.Employee

WHERE

ManagerID ISNULL

UNIONALL

SELECT

e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID,Level+ 1,Path+'/'+CAST(e.EmployeeID ASVARCHAR(MAX))

FROM

HumanResources.Employee AS e

JOIN

DirectReports AS d

ON

e.ManagerID = d.EmployeeID

)

SELECT*

FROM DirectReports

ORDERBYPath

Hey, Thanks for your reply.

|||

Try something like this:

WITH DirectReports (ManagerID, EmployeeID, Title,Level,Path)

AS

(

SELECT ManagerID, EmployeeID, Title, 0 ASLevel,CAST(EmployeeID ASVARCHAR(MAX))ASPath

FROM HumanResources.Employee

WHERE ManagerID ISNULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title,Level+ 1,Path+'/'+CAST(e.EmployeeID ASVARCHAR(MAX))

FROM HumanResources.Employee AS e

JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT*

FROM DirectReports

ORDERBYPath

FORXMLRAW,ROOT('Emp')

I think that is what you are looking for, but if not your best bet is to play with the FOR XML clause. I will also keep playing with it as well to make sure.

Scott

create xml from sql query

Hi I have a "configuration" table in a SQL database it has the parentid and itemid to create a tree, I need to create another tree froma an xml but i dont know how to relate them in the xml file , because when i create the xmlfile all the items are alike, any hints?

Thanks and regards.

To get started you can look at the different FOR XML modes in SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

FOR XML PATH is generally the one that give the most power with the least complexity:
http://msdn2.microsoft.com/en-us/library/ms189885.aspx

For more specific assistance, give us a little more context, about how your data is stored and how your rows are related to one another.

|||

The table has the tree structure, id, parent id, name where the parent id marks the realtion between the childs, when the parent id is null, it means that this is a parent node. I need to export it to an xml because one of my controls do not accept sql databinding only xml files.

Thanks and best regards.

|||

If you have a recursive structure like this, you can also investigate using CTE's or "Common Table Expressions" to generate recursive structures. Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You would want to write yours so that at each level, each nodes' child XML is nested within it.

|||

Great. I got resultset of parent child relationship using CTE . Now I want xml output as below

<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>

Can you suggest idle way for same?

|||As posted earlier, now use the FOR XML to return the result formatted as XML. If you can, post your CTE and we can show you how to modify that to return the xml you desire.|||

Here is query

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID,Level,Path)

AS

(

SELECT

ManagerID, EmployeeID, Title, DepartmentID, 0 ASLevel,CAST(EmployeeID ASVARCHAR(MAX))ASPath

FROM

HumanResources.Employee

WHERE

ManagerID ISNULL

UNIONALL

SELECT

e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID,Level+ 1,Path+'/'+CAST(e.EmployeeID ASVARCHAR(MAX))

FROM

HumanResources.Employee AS e

JOIN

DirectReports AS d

ON

e.ManagerID = d.EmployeeID

)

SELECT*

FROM DirectReports

ORDERBYPath

Hey, Thanks for your reply.

|||

Try something like this:

WITH DirectReports (ManagerID, EmployeeID, Title,Level,Path)

AS

(

SELECT ManagerID, EmployeeID, Title, 0 ASLevel,CAST(EmployeeID ASVARCHAR(MAX))ASPath

FROM HumanResources.Employee

WHERE ManagerID ISNULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title,Level+ 1,Path+'/'+CAST(e.EmployeeID ASVARCHAR(MAX))

FROM HumanResources.Employee AS e

JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT*

FROM DirectReports

ORDERBYPath

FORXMLRAW,ROOT('Emp')

I think that is what you are looking for, but if not your best bet is to play with the FOR XML clause. I will also keep playing with it as well to make sure.

Scott

create xml from sql query

Hi I have a "configuration" table in a SQL database it has the parentid and itemid to create a tree, I need to create another tree froma an xml but i dont know how to relate them in the xml file , because when i create the xmlfile all the items are alike, any hints?

Thanks and regards.

To get started you can look at the different FOR XML modes in SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

FOR XML PATH is generally the one that give the most power with the least complexity:
http://msdn2.microsoft.com/en-us/library/ms189885.aspx

For more specific assistance, give us a little more context, about how your data is stored and how your rows are related to one another.

|||

The table has the tree structure, id, parent id, name where the parent id marks the realtion between the childs, when the parent id is null, it means that this is a parent node. I need to export it to an xml because one of my controls do not accept sql databinding only xml files.

Thanks and best regards.

|||

If you have a recursive structure like this, you can also investigate using CTE's or "Common Table Expressions" to generate recursive structures. Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You would want to write yours so that at each level, each nodes' child XML is nested within it.

|||

Great. I got resultset of parent child relationship using CTE . Now I want xml output as below

<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>

Can you suggest idle way for same?

|||As posted earlier, now use the FOR XML to return the result formatted as XML. If you can, post your CTE and we can show you how to modify that to return the xml you desire.|||

Here is query

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)

AS

(

SELECT

ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path

FROM

HumanResources.Employee

WHERE

ManagerID IS NULL

UNION ALL

SELECT

e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))

FROM

HumanResources.Employee AS e

JOIN

DirectReports AS d

ON

e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

ORDER BY Path

Hey, Thanks for your reply.

|||

Try something like this:

WITH DirectReports (ManagerID, EmployeeID, Title, Level, Path)

AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))

FROM HumanResources.Employee AS e

JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

ORDER BY Path

FOR XML RAW, ROOT('Emp')

I think that is what you are looking for, but if not your best bet is to play with the FOR XML clause. I will also keep playing with it as well to make sure.

Scott

Create XML file with SS Management Studio Express

Hi Folks,

I have this problem using SQL Server Management Studio Express.
When browse query/table with XML type column I cannot create manualy XML field.
Is that possible using built-in XML editor ?

I make following steps:
1. Use AdventureWorks example Database
2. Create New Query on Person.Contact table

SELECT ContactID, Title, FirstName, AdditionalContactInfo
FROM Person.Contact

3. Execute query
4. Then : first 10 records contain XML data in AdditionalContactInfo field
5. I cannot insert manualy new XML data into record 11,12 e.t.c

I thing it is possible by built-in XML editor, but HOW ?

Is it possible to change such field using MS Access version 2002 ?

Thanks in advance !

Hi Aleko_b,

You can't directly edit the XML with SQL Server Management Studio or the Express version either. We are considering this functionality in a future release. You need to construct an INSERT statement within the T-SQL Editor and copy/paste the XML from the XML editor.

As far as Access 2003 is concerned, you will want to create a Linked Table via ODBC to the table in question. The XML field will look like a Memo field that you can edit for changing the contents of the XML field. Just be careful not to modify the tags. ;-)

Thank you,
Bill Ramos, Lead PM, SQL Server Manageabilty

|||

I just discovered the "XML Editor" today. Is there an instruction guide or a list of supported features available?

TIA,

barkingdog

Create XML file with SS Management Studio Express

Hi Folks,

I have this problem using SQL Server Management Studio Express.
When browse query/table with XML type column I cannot create manualy XML field.
Is that possible using built-in XML editor ?

I make following steps:
1. Use AdventureWorks example Database
2. Create New Query on Person.Contact table

SELECT ContactID, Title, FirstName, AdditionalContactInfo
FROM Person.Contact

3. Execute query
4. Then : first 10 records contain XML data in AdditionalContactInfo field
5. I cannot insert manualy new XML data into record 11,12 e.t.c

I thing it is possible by built-in XML editor, but HOW ?

Is it possible to change such field using MS Access version 2002 ?

Thanks in advance !

Hi Aleko_b,

You can't directly edit the XML with SQL Server Management Studio or the Express version either. We are considering this functionality in a future release. You need to construct an INSERT statement within the T-SQL Editor and copy/paste the XML from the XML editor.

As far as Access 2003 is concerned, you will want to create a Linked Table via ODBC to the table in question. The XML field will look like a Memo field that you can edit for changing the contents of the XML field. Just be careful not to modify the tags. ;-)

Thank you,
Bill Ramos, Lead PM, SQL Server Manageabilty

|||

I just discovered the "XML Editor" today. Is there an instruction guide or a list of supported features available?

TIA,

barkingdog

Create XML file with SS Management Studio Express

Hi Folks,

I have this problem using SQL Server Management Studio Express.
When browse query/table with XML type column I cannot create manualy XML field.
Is that possible using built-in XML editor ?

I make following steps:
1. Use AdventureWorks example Database
2. Create New Query on Person.Contact table

SELECT ContactID, Title, FirstName, AdditionalContactInfo
FROM Person.Contact

3. Execute query
4. Then : first 10 records contain XML data in AdditionalContactInfo field
5. I cannot insert manualy new XML data into record 11,12 e.t.c

I thing it is possible by built-in XML editor, but HOW ?

Is it possible to change such field using MS Access version 2002 ?

Thanks in advance !

Hi Aleko_b,

You can't directly edit the XML with SQL Server Management Studio or the Express version either. We are considering this functionality in a future release. You need to construct an INSERT statement within the T-SQL Editor and copy/paste the XML from the XML editor.

As far as Access 2003 is concerned, you will want to create a Linked Table via ODBC to the table in question. The XML field will look like a Memo field that you can edit for changing the contents of the XML field. Just be careful not to modify the tags. ;-)

Thank you,
Bill Ramos, Lead PM, SQL Server Manageabilty

|||

I just discovered the "XML Editor" today. Is there an instruction guide or a list of supported features available?

TIA,

barkingdog