MS SQL XML - XML Output Encoding

Author

XML Output Encoding

Calvin Willman

2005-10-27, 9:25 am

I'm trying to create XML Doc from 2 Tables on the Server.
Managed to get Query to run, and produce XML, but when I copy the result
into an XML doc, some of it isn't correctly written. For example, it may
convert & to & and will put p; in places.

Why is this and how can I just get it output what's in the tables, without
messing around with the data.

I'm using Query Analyser to do this and have the output set to text. I've
also set the maximum number of characters allowed to 8194 as the results
were being cut before the end.

I'm new to SQLXML

Thanks

Here is my query

SELECT 1 as Tag,
NULL as Parent,
cc_patterns.patternid as [pattern!1!id],
cc_patterns.patternname as & #91;pattern!1!name],

null as [product!2!id],
null as [product!2!name]
FROM cc_patterns

UNION All

SELECT 2, 1,
cc_patterns.patternid,
cc_patterns.patternname,
cc_products.productid,
cc_products.productname

FROM cc_patterns, cc_products

WHERE cc_patterns.patternid = cc_products.patternid
ORDER BY [pattern!1!name]

FOR XML Explicit

Michael Rys [MSFT]

2005-10-27, 9:25 am

Hi Calvin

First, XML requires that & is represented as &. Any XML parser will
understand that and give you back & if you as for the text content.

Also note that the Query Analyser is not good at supporting XML documents.
If you want to get the XML back without problem (e.g., the newlines ever
+-2030 characters), I recommend that you either use the SQLXML ISAPI and
templates or write a small ADO script that uses the CommandStream
interface...

Or use the SQL Server 2005 capabilities (if you can upgrade).

Best regards
Michael

"Calvin Willman" < calvinwillman@orange
.net> wrote in message
news:BF72F991. 734%calvinwillman@or
ange.net...
> I'm trying to create XML Doc from 2 Tables on the Server.
> Managed to get Query to run, and produce XML, but when I copy the result
> into an XML doc, some of it isn't correctly written. For example, it may
> convert & to & and will put p; in places.
>
> Why is this and how can I just get it output what's in the tables, without
> messing around with the data.
>
> I'm using Query Analyser to do this and have the output set to text. I've
> also set the maximum number of characters allowed to 8194 as the results
> were being cut before the end.
>
> I'm new to SQLXML
>
> Thanks
>
> Here is my query
>
>
> SELECT 1 as Tag,
> NULL as Parent,
> cc_patterns.patternid as [pattern!1!id],
> cc_patterns.patternname as & #91;pattern!1!name],

> null as [product!2!id],
> null as [product!2!name]
> FROM cc_patterns
>
> UNION All
>
> SELECT 2, 1,
> cc_patterns.patternid,
> cc_patterns.patternname,
> cc_products.productid,
> cc_products.productname
>
> FROM cc_patterns, cc_products
>
> WHERE cc_patterns.patternid = cc_products.patternid
> ORDER BY [pattern!1!name]
>
> FOR XML Explicit
>
>

Sreejith

2005-10-27, 9:25 am

Hi, specify it as a CDATA section where you receice special characters...

ie... if id has special character.. try below query

SELECT 1 as Tag,
NULL as Parent,
cc_patterns.patternid as & #91;pattern!1!id!!cd
ata],
cc_patterns.patternname as & #91;pattern!1!name],

null as [product!2!id],
null as [product!2!name]
FROM cc_patterns

UNION All

SELECT 2, 1,
cc_patterns.patternid,
cc_patterns.patternname,
cc_products.productid,
cc_products.productname

FROM cc_patterns, cc_products

WHERE cc_patterns.patternid = cc_products.patternid
ORDER BY [pattern!1!name]

FOR XML Explicit

SELECT 1 as Tag,
NULL as Parent,
cc_patterns.patternid as [pattern!1!id],
cc_patterns.patternname as & #91;pattern!1!name],

null as [product!2!id],
null as [product!2!name]
FROM cc_patterns

UNION All

SELECT 2, 1,
cc_patterns.patternid,
cc_patterns.patternname,
cc_products.productid,
cc_products.productname

FROM cc_patterns, cc_products

WHERE cc_patterns.patternid = cc_products.patternid
ORDER BY [pattern!1!name]

FOR XML Explicit

"Calvin Willman" wrote:

> I'm trying to create XML Doc from 2 Tables on the Server.
> Managed to get Query to run, and produce XML, but when I copy the result
> into an XML doc, some of it isn't correctly written. For example, it may
> convert & to & and will put p; in places.
>
> Why is this and how can I just get it output what's in the tables, without
> messing around with the data.
>
> I'm using Query Analyser to do this and have the output set to text. I've
> also set the maximum number of characters allowed to 8194 as the results
> were being cut before the end.
>
> I'm new to SQLXML
>
> Thanks
>
> Here is my query
>
>
> SELECT 1 as Tag,
> NULL as Parent,
> cc_patterns.patternid as [pattern!1!id],
> cc_patterns.patternname as & #91;pattern!1!name],

> null as [product!2!id],
> null as [product!2!name]
> FROM cc_patterns
>
> UNION All
>
> SELECT 2, 1,
> cc_patterns.patternid,
> cc_patterns.patternname,
> cc_products.productid,
> cc_products.productname
>
> FROM cc_patterns, cc_products
>
> WHERE cc_patterns.patternid = cc_products.patternid
> ORDER BY [pattern!1!name]
>
> FOR XML Explicit
>
>
>

Peter Flynn

2005-10-27, 9:26 am

Sreejith wrote:

> Hi, specify it as a CDATA section where you receice special characters...

But first read the FAQ: http://xml.silmaril.ie/authors/cdata

///Peter

Calvin Willman

2005-10-27, 9:26 am

Thanks for your reply.

Managed to get this working... Well kind of. A quick fix at least is copying
the result to Word, doing a Replace All on the Paragraph Character, saving
as plain text. Then it pastes to Visual Studio fine for creating the XML.
It's for a sitemap so doesn't need to be repeated often, but I'll have a
look at what you've suggested.

Thanks again

On 13/10/05 02:48, in article #5rfGh5zFHA.664@tk2msftngp13.phx.gbl, "Michael
Rys [MSFT]" <mrys@online.microsoft.com> wrote:

> Hi Calvin
>
> First, XML requires that & is represented as &. Any XML parser will
> understand that and give you back & if you as for the text content.
>
> Also note that the Query Analyser is not good at supporting XML documents.
> If you want to get the XML back without problem (e.g., the newlines ever
> +-2030 characters), I recommend that you either use the SQLXML ISAPI and
> templates or write a small ADO script that uses the CommandStream
> interface...
>
> Or use the SQL Server 2005 capabilities (if you can upgrade).
>
> Best regards
> Michael
>
>
> "Calvin Willman" < calvinwillman@orange
.net> wrote in message
> news:BF72F991. 734%calvinwillman@or
ange.net...
>
>

Calvin Willman

2005-10-27, 9:26 am

Will have a look. Thanks.

On 13/10/05 23:15, in article 3r84kpFi2s1vU2@indiv
idual.net, "Peter Flynn"
<peter.nosp@m.silmaril.ie> wrote:

> Sreejith wrote:
>
>
> But first read the FAQ: http://xml.silmaril.ie/authors/cdata
>
> ///Peter
>

Sponsored Links