Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.

SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT

in above query misc1 column may contain some non printable ascii
characters.

But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"

DECLARE @.XMLMessage XML

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)

I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.

Let me know if something dose'nt make sense

any help is appreciated

Thanks

You should ask the question in the SQL Server XML Forums.

Can you base64 encode your varbinary data instead of using CDATA section?

SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML AUTO, BINARY BASE64)

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1AS tag
NULLAS parent,
template_idAS [Emailqueue!1!user_id],
misc1AS [Emailqueue!1!!cdata]
FROMEmailqueue WITH (NOLOCK)
WHEREqueue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanks
prashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
XML FAQ: http://xml.silmaril.ie/
|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant
|||If you need to transport these unprintable characters in XML, you need to
cast the column to varbinary(max) and transport it as base64 encoded binary
data.
Best regards
Michael
<prashant.k.jain@.gmail.com> wrote in message
news:1162602860.975903.50180@.m73g2000cwd.googlegro ups.com...
> Peter Flynn wrote:
> Just want to make sure is their no way other then filtering out or
> encoding in some other manner. I thought their musst be some way within
> SQL server to handel this.
> Thanks,
> Prashant
>
sql

Problem with CDATA section in FOR XML EXPLICIT

Hi,
I am trying to create a XML out of sql 2005 database using FOR XML. I
need to create XML for tables which may contain data having
non-printable ascii characters (1-32 ascii character). I found FOR XML
AUTO failes to genrate this XML, but i can genrate XML using CDATA
section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii
characters.
But i need to store this XML data in some sql XML variable as i need to
pass it to store procedure which expects an xml input. While doing
following i gets an error saying "illegal xml character"
DECLARE @.XMLMessage XML
SET @.XMLMessage = (SELECT
1 AS tag
NULL AS parent,
template_id AS [Emailqueue!1!user_id],
misc1 AS [Emailqueue!1!!cdata]
FROM Emailqueue WITH (NOLOCK)
WHERE queue_id = -2147483169
FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even
need to process same message using OPENXML on differen database server.
Again which will need well formated XML.
Let me know if something dose'nt make sense
any help is appreciated
Thanksprashant.k.jain@.gmail.com wrote:
> Hi,
> I am trying to create a XML out of sql 2005 database using FOR XML. I
> need to create XML for tables which may contain data having
> non-printable ascii characters (1-32 ascii character).
[...]
> But i need to store this XML data in some sql XML variable as i need to
> pass it to store procedure which expects an xml input. While doing
> following i gets an error saying "illegal xml character"
Using CDATA sections only protects the content against being parsed for
markup. It still has to conform to the XML rules on characters, so
control characters are still illegal: you'll have to filter them out or
encode them in some way.
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Peter Flynn wrote:
> prashant.k.jain@.gmail.com wrote:
> [...]
> Using CDATA sections only protects the content against being parsed for
> markup. It still has to conform to the XML rules on characters, so
> control characters are still illegal: you'll have to filter them out or
> encode them in some way.
> ///Peter
> --
> XML FAQ: http://xml.silmaril.ie/
Just want to make sure is their no way other then filtering out or
encoding in some other manner. I thought their musst be some way within
SQL server to handel this.
Thanks,
Prashant

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>
Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
--
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with ampersand "&amp;"

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (using
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.
Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&amp;"
|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&amp;"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?

Joe Fawcett - XML MVP
http://joe.fawcett.name
|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value><![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entitized.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Rob
sorry for the late reply.
But you should never extract values from an XML document using
CONVERT(nvarchar(MAX), T.c.query('data(Value)'))
use
T.c.value('Value', 'nvarchar(MAX)') instead. That should take care of your
encoding issue.
Best regards
Michael
"Rob Epler" <Rob Epler@.discussions.microsoft.com> wrote in message
news:BFFD350A-2B24-4133-8CEB-FCF5F700D560@.microsoft.com...
> I'm using XQUERY to slog through an XML doc, and dump the value of a CDATA
> element into an NVARCHAR(MAX) field. The problem I'm running into is that
> when an ampersand goes into that field, what I get back out in a query
> (using
> FOR XML) is "&".
> using:
> CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
> The above stores a "&" in the field, even though the input was:
> <![CDATA[&]>
> Any insight is much appreciated.
>

Problem with ampersand "&amp;"

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (usin
g
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value>
<![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entiti
zed.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/sql

problem with adding sql:relation

hello,
I'm trying to load xml data using SQLXMLBulkLoad
my xml schema is:
<xsd:element name="OrderDetail"/>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName"/>
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>
example xml:
<OrderDetail>
<CompanyID>123</CompanyID>
<CompanyName>ABC</CompanyName>
<OrderDate>2002-10-10</OrderDate>
<Price>100$</Price>
</OrderDetail>
I've 2 tables which I want to update using this xml file:
Table Company:
===========
CompanyID (primary)
CompanyName
Table Orders:
==========
CompanyID (primary, foreign from Company)
OrderDate
Price
how do I put the "sql:relation" annotation in 'OrderDetail' when its data should update the two tables in fields that are not the primary-key ? (CompanyName --> Company
and
OrderDate,Price --> Orders)
Best Regards
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
This should work:
<xsd:element name="OrderDetail" sql:relation="Orders" />
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName" sql:relation="Company"
sql:relationship="OrdersCompany" />
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="OrdersCompany"
parent="Orders"
parent-key="CompanyID"
child="Company"
child-key="CompanyID" />
</xsd:appinfo>
</xsd:annotation>
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
sql

problem with adding sql:relation

hello,
I'm trying to load xml data using SQLXMLBulkLoad
my xml schema is:
<xsd:element name="OrderDetail"/>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName"/>
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>
example xml:
<OrderDetail>
<CompanyID>123</CompanyID>
<CompanyName>ABC</CompanyName>
<OrderDate>2002-10-10</OrderDate>
<Price>100$</Price>
</OrderDetail>
I've 2 tables which I want to update using this xml file:
Table Company:
===========
CompanyID (primary)
CompanyName
Table Orders:
==========
CompanyID (primary, foreign from Company)
OrderDate
Price
how do I put the "sql:relation" annotation in 'OrderDetail' when its data sh
ould update the two tables in fields that are not the primary-key ? (Company
Name --> Company
and
OrderDate,Price --> Orders)
Best Regards
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.This should work:
<xsd:element name="OrderDetail" sql:relation="Orders" />
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName" sql:relation="Company"
sql:relationship="OrdersCompany" />
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="OrdersCompany"
parent="Orders"
parent-key="CompanyID"
child="Company"
child-key="CompanyID" />
</xsd:appinfo>
</xsd:annotation>
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/

problem with 2005 xml datasource that is xml render of a 2000 report

Hi,
I'm trying to setup an xml datasource in ssrs 2005. The xml datasource is
an xml render from a ssrs 2000 report.
The connection string is a url which works fine when I put it into a
standalone browser (prompts me to save the xml output).
However when I try to then do a basic Query String for a dataset, I get an
error back from ssrs 2000
"The value provided for the report parameter 'EndDate' is not valid for its
type. (rsReportParameterTypeMismatch) "
My url is url encoded, and has the name value pair
"rs:ParameterLanguage=en-GB" defined
Any ideas on troubleshooting this?
Thanks
MartinTurns out I wasn't affecting the url when I thought I was.
"Martin" <x@.y.z> wrote in message
news:%23pgeok9OHHA.3268@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I'm trying to setup an xml datasource in ssrs 2005. The xml datasource is
> an xml render from a ssrs 2000 report.
> The connection string is a url which works fine when I put it into a
> standalone browser (prompts me to save the xml output).
> However when I try to then do a basic Query String for a dataset, I get an
> error back from ssrs 2000
> "The value provided for the report parameter 'EndDate' is not valid for
> its type. (rsReportParameterTypeMismatch) "
> My url is url encoded, and has the name value pair
> "rs:ParameterLanguage=en-GB" defined
> Any ideas on troubleshooting this?
> Thanks
> Martin
>

Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

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

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

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

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike

Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

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

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

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

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike

Problem Using XML BASE64 encoding & SQL Server

Here is what I am trying to do.

I allow users to upload images from client side. This is the code I m using to load image into a client side xml document element

var node1 = xmlData.createElement("PHOTO");
node1.dataType = "bin.base64";
// Open stream object and read source file
adoStream.Type = 1; // 1=adTypeBinary
adoStream.Open();
adoStream.LoadFromFile(filename);

// Store file content and filename into XML nodes
node1.nodeTypedValue = adoStream.Read(-1); // -1=adReadAll
document.all("INVST_PHOTO").src = adoStream.Read(-1);
node2.nodeTypedValue = filename;

Now after that I extract this Image and insert it into Sql server using a stored procedure.

Dim ImgBuff() As Byte

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").Text

' Add null termination:
ReDim Preserve ImgBuff(0 To UBound(ImgBuff) + 2) As Byte

' Get the pointer to the string:
Dim lPtrString As Long
lPtrString = VarPtr(ImgBuff(0))

objCmd.Parameters("@.pIMG_PHOTO").AppendChunk ImgBuff().

I am Sucessfully able to store it in the database fileld type of Image. Now I am using XML: to retrieve images from the database. Here is how the SQL looks like

SELECT
1 AS TAG, NULL AS PARENT,
IMG_PHOT AS [PHOTO!1!PHOTO!ELEMENT]
FROM PHOTO
FOR XML EXPLICIT , BINARY BASE64
The problem I am runing into is that content of the IMG_PHOTO are not the same after saving and retreival.

Here is how the contents are prior to inserting into database

/9j/4AAQSkZJRgABAgAAZABkAAD/7AARRHVja3kAAQAEAAAAPAAA/+4ADkFkb2JlAGTAAAAAAf/bAIQABgQEBAUEBgUFBgkGBQYJCwgGBggLDAoKCwoKDBand here is how they look after retrieval

LwA5AGoALwA0AEEAQQBRAFMAawBaAEoAUgBnAEEAQgBBAGcAQQBBAFoAQQBCAGsAQQBBAEQALwA3AEEAQQBSAFIASABWAGoAYQAzAGsAQQB

I expected them to look same. I guess what I am doing is encoding the contents twice once I load it and once I am retreiving it from xml. Can please somebody help me out with this so that I can have the same content on both ocassion.

I am using Javascript/VB6/SQLServer 2000

I resolved it Instead of using

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").Tex

I shoudl have used it Instead of using

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").nodeTypeValue

which gives me base64 encoding data. VB string gives us Unicode data.

Problem Using XML BASE64 encoding & SQL Server

Here is what I am trying to do.

I allow users to upload images from client side. This is the code I m using to load image into a client side xml document element

var node1 = xmlData.createElement("PHOTO");
node1.dataType = "bin.base64";
// Open stream object and read source file
adoStream.Type = 1; // 1=adTypeBinary
adoStream.Open();
adoStream.LoadFromFile(filename);

// Store file content and filename into XML nodes
node1.nodeTypedValue = adoStream.Read(-1); // -1=adReadAll
document.all("INVST_PHOTO").src = adoStream.Read(-1);
node2.nodeTypedValue = filename;

Now after that I extract this Image and insert it into Sql server using a stored procedure.

Dim ImgBuff() As Byte

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").Text

' Add null termination:
ReDim Preserve ImgBuff(0 To UBound(ImgBuff) + 2) As Byte

' Get the pointer to the string:
Dim lPtrString As Long
lPtrString = VarPtr(ImgBuff(0))

objCmd.Parameters("@.pIMG_PHOTO").AppendChunk ImgBuff().

I am Sucessfully able to store it in the database fileld type of Image. Now I am using XML: to retrieve images from the database. Here is how the SQL looks like

SELECT
1 AS TAG, NULL AS PARENT,
IMG_PHOT AS [PHOTO!1!PHOTO!ELEMENT]
FROM PHOTO
FOR XML EXPLICIT , BINARY BASE64
The problem I am runing into is that content of the IMG_PHOTO are not the same after saving and retreival.

Here is how the contents are prior to inserting into database

/9j/4AAQSkZJRgABAgAAZABkAAD/7AARRHVja3kAAQAEAAAAPAAA/+4ADkFkb2JlAGTAAAAAAf/bAIQABgQEBAUEBgUFBgkGBQYJCwgGBggLDAoKCwoKDBand here is how they look after retrieval

LwA5AGoALwA0AEEAQQBRAFMAawBaAEoAUgBnAEEAQgBBAGcAQQBBAFoAQQBCAGsAQQBBAEQALwA3AEEAQQBSAFIASABWAGoAYQAzAGsAQQB

I expected them to look same. I guess what I am doing is encoding the contents twice once I load it and once I am retreiving it from xml. Can please somebody help me out with this so that I can have the same content on both ocassion.

I am using Javascript/VB6/SQLServer 2000

I resolved it Instead of using

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").Tex

I shoudl have used it Instead of using

ImgBuff = objDOMDocument.selectSingleNode("SACWIS/INVST/INVST_PHOTO/PHOTO").nodeTypeValue

which gives me base64 encoding data. VB string gives us Unicode data.

Problem using Portugueses characters

Dear friends,
I got a XML string that I need to convert in a select statement. I'm using
OPENXML to do it and everything works well, but when I write Portugueses
words in my XML document(for sample: valida?o) is shown something weird
like: valida?o
I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
on, but nothing works. Check out my code.
declare @.idoc int
declare @.doc_xml_nota varchar(8000)
set @.doc_xml_nota = '
<?xml version="1.0" encoding="ISO-8859-1"?>
<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida?o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
select *
FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico', 2)
WITH
(
des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
)
EXEC sp_xml_removedocument @.idoc
Thanks,
Euler Almeida
Message posted via http://www.sqlmonster.com
Can you use Unicode characters instead?
E.g,
declare @.doc_xml_nota nvarchar(8000)
set @.doc_xml_nota = N'<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida?o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet> '
Otherwise, you need to make sure that the database collation and the
encoding inside the XML and the character codes are all aligned...
Best regards
Michael
"Euler Almeida via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cdc1fae2f9d34240ac26017dcba8b8aa@.SQLMonster.c om...
> Dear friends,
> I got a XML string that I need to convert in a select statement. I'm using
> OPENXML to do it and everything works well, but when I write Portugueses
> words in my XML document(for sample: valida?o) is shown something weird
> like: valida?o
> I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
> on, but nothing works. Check out my code.
> declare @.idoc int
> declare @.doc_xml_nota varchar(8000)
> set @.doc_xml_nota = '
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <NewDataSet>
> <nota_fiscal_fornecedor_setor_publico>
> <des_informacao_complement>valida?o </des_informacao_complement>
> </nota_fiscal_fornecedor_setor_publico>
> </NewDataSet>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
> select *
> FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico',
> 2)
> WITH
> (
> des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
> )
> EXEC sp_xml_removedocument @.idoc
>
> Thanks,
> Euler Almeida
> --
> Message posted via http://www.sqlmonster.com

Problem using Portugueses characters

Dear friends,
I got a XML string that I need to convert in a select statement. I'm using
OPENXML to do it and everything works well, but when I write Portugueses
words in my XML document(for sample: valida'o) is shown something weird
like: valida'o
I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
on, but nothing works. Check out my code.
---
declare @.idoc int
declare @.doc_xml_nota varchar(8000)
set @.doc_xml_nota = '
<?xml version="1.0" encoding="ISO-8859-1"?>
<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida'o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
select *
FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico', 2)
WITH
(
des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
)
EXEC sp_xml_removedocument @.idoc
Thanks,
Euler Almeida
Message posted via http://www.webservertalk.comCan you use Unicode characters instead?
E.g,
declare @.doc_xml_nota nvarchar(8000)
set @.doc_xml_nota = N'<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida'o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet> '
Otherwise, you need to make sure that the database collation and the
encoding inside the XML and the character codes are all aligned...
Best regards
Michael
"Euler Almeida via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:cdc1fae2f9d34240ac26017dcba8b8aa@.SQ
webservertalk.com...
> Dear friends,
> I got a XML string that I need to convert in a select statement. I'm using
> OPENXML to do it and everything works well, but when I write Portugueses
> words in my XML document(for sample: valida'o) is shown something weird
> like: valida'o
> I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
> on, but nothing works. Check out my code.
> ---
> declare @.idoc int
> declare @.doc_xml_nota varchar(8000)
> set @.doc_xml_nota = '
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <NewDataSet>
> <nota_fiscal_fornecedor_setor_publico>
> <des_informacao_complement>valida'o </des_informacao_complement>
> </nota_fiscal_fornecedor_setor_publico>
> </NewDataSet>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
> select *
> FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico',
> 2)
> WITH
> (
> des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
> )
> EXEC sp_xml_removedocument @.idoc
>
> Thanks,
> Euler Almeida
> --
> Message posted via http://www.webservertalk.com

Problem using dates in XMLtoFLFF Transformation

I am having a bit of trouble transforming a XML (xs:date) type to the DBDATE in my FLFF. I believe the problem is because the SSIS DF (Metadeta) converts xs:date to DT_DBTIMESTAMP which becomes 01/01/2005 00:00:00 -- appending the time. So when I try to transform in my fixed length flat file, it throws a truncation error when the length is set to 10. (which is the length of date)

Is it possible to (A) use some other xs format that changes DF Metadata to only use date and not DT_DBTIMESTAMP, or (B) is there a field type in the destination FLFF Connection that will allow a length of 10 without throwing a truncation error?The only output column date type supported by the XmlSrc is DT_DBTIMESTAMP. You could try changing the output column type to a string in the advanced editor, and set it to the length you want, and set the truncation disposition to ignore truncations.

Or, You could place a Data Conversion transform on the output of the XmlSrc, and either convert the DT_DBTIMESTAMP column to DT_DBDATE (which does not include the time), or actually convert the DT_DBTIMESTAMP to a string of the desired length and set the truncation disposition to ignore truncations.

Let me know if this solves your problem, or you need a different solution.

Mark|||Thanks Mark the feedback. Yep, I ended up implementing the first solution and works ok. The second solution is a good one as well but seems a bit too much, we will just enforce no date_type validations for now. Do you (or anyone else) know if this ability will ever be natively incorporated into SSIS?|||Do you mean, will it be possible to map the xml input to a date type other than just DT_DBTIMESTAMP directly in the XmlSrc adapter? If so, I know of no plans for that, but it would be great if you could open a DCR bug to that effect.