generating XML to validated against the schema

hi,
I have a sample xml data and sample xsd as follows: the xsd is really big
and runs into 4 pages. I need to stored data into the data base
and generate xml that validates against this schema.
the sqlxml bulk load and view mapper fails to recognize the xsd fine.
Can some one point me to the right direction .
Infact storing is working but generating the xml
using EXPLICIT option is really a pain.
Regards,
Balajee
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="StrategicPerformanceContract"
targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:documentation>Strategic Performance Contract</xs:documentation>
</xs:annotation>
<xs:element name="root" type="contractType" />
<xs:complexType name="contractType">
<xs:sequence>
<xs:element name="transactionHeader" type="transactionHeaderType" />
<xs:element name="documentHeader" type="documentHeaderType" />
<xs:element name="strategicDirection" type="strategicDirectionType" />
<xs:element name="strategicChoices" type="strategicChoicesType" />
<xs:element name="riskManagement" type="riskManagementType" />
<xs:element name="performanceContract" type="performanceContractType" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="transactionHeaderType">
<xs:sequence>
<xs:element name="userName" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="documentHeaderType">
<xs:sequence>
<xs:element name="contractType" type="xs:ID" />
<xs:element name="abbr" type="xs:string" />
<xs:element name="leftTitle1" type="xs:string" />
<xs:element name="leftTitle2" type="xs:string" />
<xs:element name="rightTitle1" type="xs:string" />
<xs:element name="asofDate" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="strategicDirectionType">
<xs:sequence>
<xs:element name="header" type="headerType" />
<xs:element name="strategicObjective" type="strategicObjectiveType"
minOccurs="0" maxOccurs="unbounded" />
<xs:element name="footnotes" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="headerType">
<xs:sequence>
<xs:element name="vpuMission" type="xs:string" />
<xs:element name="strategicContext" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="strategicObjectiveType">
<xs:sequence>
<xs:element name="objective" type="objectiveType" />
<xs:element name="subObjective" type="objectiveType" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
What are the problems you get when using the Schema and adding mapping
annotations?
Thanks
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
> hi,
> I have a sample xml data and sample xsd as follows: the xsd is really big
> and runs into 4 pages. I need to stored data into the data base
> and generate xml that validates against this schema.
> the sqlxml bulk load and view mapper fails to recognize the xsd fine.
> Can some one point me to the right direction .
> Infact storing is working but generating the xml
> using EXPLICIT option is really a pain.
> Regards,
> Balajee
>
> <?xml version="1.0" encoding="utf-8" ?>
> <xs:schema id="StrategicPerformanceContract"
> targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:documentation>Strategic Performance Contract</xs:documentation>
> </xs:annotation>
> <xs:element name="root" type="contractType" />
> <xs:complexType name="contractType">
> <xs:sequence>
> <xs:element name="transactionHeader" type="transactionHeaderType" />
> <xs:element name="documentHeader" type="documentHeaderType" />
> <xs:element name="strategicDirection" type="strategicDirectionType" />
> <xs:element name="strategicChoices" type="strategicChoicesType" />
> <xs:element name="riskManagement" type="riskManagementType" />
> <xs:element name="performanceContract" type="performanceContractType" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="transactionHeaderType">
> <xs:sequence>
> <xs:element name="userName" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="documentHeaderType">
> <xs:sequence>
> <xs:element name="contractType" type="xs:ID" />
> <xs:element name="abbr" type="xs:string" />
> <xs:element name="leftTitle1" type="xs:string" />
> <xs:element name="leftTitle2" type="xs:string" />
> <xs:element name="rightTitle1" type="xs:string" />
> <xs:element name="asofDate" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="strategicDirectionType">
> <xs:sequence>
> <xs:element name="header" type="headerType" />
> <xs:element name="strategicObjective" type="strategicObjectiveType"
> minOccurs="0" maxOccurs="unbounded" />
> <xs:element name="footnotes" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="headerType">
> <xs:sequence>
> <xs:element name="vpuMission" type="xs:string" />
> <xs:element name="strategicContext" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="strategicObjectiveType">
> <xs:sequence>
> <xs:element name="objective" type="objectiveType" />
> <xs:element name="subObjective" type="objectiveType" minOccurs="0"
> maxOccurs="unbounded" />
> </xs:sequence>
> </xs:complexType>
|||Michael,
I am trying to map the schema using sql viewmapper and
it gives me an error :
Error while parsing
:Incorrect definition for the root element in schema.
Line 7 Position 63
xmlns:xs="http://www.w3.org/2001/XMLSchema">
regards,
Balajee
"Michael Rys [MSFT]" wrote:

> What are the problems you get when using the Schema and adding mapping
> annotations?
> Thanks
> Michael
> "balajee" <balajee@.discussions.microsoft.com> wrote in message
> news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
>
>
|||Unfortunately, the SQL Viewmapper only works for the older Microsoft schema
language known as XDR and not with the W3C XML Schema language (that you
seem to be using). This unfortunately means that you either need a
third-party tool vendor or notepad.
Best regards
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:0EA7E4AD-F94D-4021-8A22-FE327F1B786B@.microsoft.com...[vbcol=seagreen]
> Michael,
> I am trying to map the schema using sql viewmapper and
> it gives me an error :
> Error while parsing
> :Incorrect definition for the root element in schema.
> Line 7 Position 63
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
>
> regards,
> Balajee
>
> "Michael Rys [MSFT]" wrote:
sql

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie