Importing XML Sample file and XML Schema

I recently received a schema file from a vendor to import into my SQL Server
.
I am new to XML and have been reading all I can. I tried doing a Bulk Load
in a DTS Package, but it does not read the schema as the correct file format
(asking me ANSI, Unicode, etc). I have a sample data file to test with but
it does not seem to be working. Can anyone give any insight on the easiest
way to do this? I will be receiving a wly feed from this vendor and will
need to import on a schedule. Thanks.Do you need to "shred" it into relational fields or keep the whole XML
document in a single field?
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>I recently received a schema file from a vendor to import into my SQL
>Server.
> I am new to XML and have been reading all I can. I tried doing a Bulk
> Load
> in a DTS Package, but it does not read the schema as the correct file
> format
> (asking me ANSI, Unicode, etc). I have a sample data file to test with
> but
> it does not seem to be working. Can anyone give any insight on the
> easiest
> way to do this? I will be receiving a wly feed from this vendor and
> will
> need to import on a schedule. Thanks.|||I need to shred it. There are multiple tables involved.
"Michael Rys [MSFT]" wrote:

> Do you need to "shred" it into relational fields or keep the whole XML
> document in a single field?
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>
>|||You have a couple of options:
Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
to your schema (see the documentation).
Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
(you need to make sure that the XML document is in an encoding compatible
with the SQL Server collation codepage).
Write your own ASP/ASP.Net mid-tier code to perform the shredding.
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>I need to shred it. There are multiple tables involved.
> "Michael Rys [MSFT]" wrote:
>|||Hello - found the issue - apparently, the:
targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAx
apta30_2.xsd"
needs to be removed from the <xs:schema> tag.
Now, can anyone tell me why? I'm not an XML expert but learning as much as I
can...
AB
"Michael Rys [MSFT]" wrote:

> You have a couple of options:
> Use the SQLXML 3.0 XML Bulkload component. You will need to add annotation
s
> to your schema (see the documentation).
> Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT paramete
r
> (you need to make sure that the XML document is in an encoding compatible
> with the SQL Server collation codepage).
> Write your own ASP/ASP.Net mid-tier code to perform the shredding.
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>
>|||The targetnamespace indicates that the elements need to belong to the
namespace to be matched by the schema definitions.
Best regards
Michael
"Illustris" <Illustris@.discussions.microsoft.com> wrote in message
news:1EC9D995-DADE-4321-B9C4-AC8A55E07E51@.microsoft.com...
> Hello - found the issue - apparently, the:
> targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutions
Axapta30_2.xsd"
> needs to be removed from the <xs:schema> tag.
> Now, can anyone tell me why? I'm not an XML expert but learning as much as
> I
> can...
> AB
> "Michael Rys [MSFT]" wrote:
>

Importing XML Sample file and XML Schema

I recently received a schema file from a vendor to import into my SQL Server.
I am new to XML and have been reading all I can. I tried doing a Bulk Load
in a DTS Package, but it does not read the schema as the correct file format
(asking me ANSI, Unicode, etc). I have a sample data file to test with but
it does not seem to be working. Can anyone give any insight on the easiest
way to do this? I will be receiving a weekly feed from this vendor and will
need to import on a schedule. Thanks.
Do you need to "shred" it into relational fields or keep the whole XML
document in a single field?
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>I recently received a schema file from a vendor to import into my SQL
>Server.
> I am new to XML and have been reading all I can. I tried doing a Bulk
> Load
> in a DTS Package, but it does not read the schema as the correct file
> format
> (asking me ANSI, Unicode, etc). I have a sample data file to test with
> but
> it does not seem to be working. Can anyone give any insight on the
> easiest
> way to do this? I will be receiving a weekly feed from this vendor and
> will
> need to import on a schedule. Thanks.
|||I need to shred it. There are multiple tables involved.
"Michael Rys [MSFT]" wrote:

> Do you need to "shred" it into relational fields or keep the whole XML
> document in a single field?
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>
>
|||You have a couple of options:
Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
to your schema (see the documentation).
Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
(you need to make sure that the XML document is in an encoding compatible
with the SQL Server collation codepage).
Write your own ASP/ASP.Net mid-tier code to perform the shredding.
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...[vbcol=seagreen]
>I need to shred it. There are multiple tables involved.
> "Michael Rys [MSFT]" wrote:
|||Hello - found the issue - apparently, the:
targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAxapta30_2.xsd"
needs to be removed from the <xs:schema> tag.
Now, can anyone tell me why? I'm not an XML expert but learning as much as I
can...
AB
"Michael Rys [MSFT]" wrote:

> You have a couple of options:
> Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
> to your schema (see the documentation).
> Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
> (you need to make sure that the XML document is in an encoding compatible
> with the SQL Server collation codepage).
> Write your own ASP/ASP.Net mid-tier code to perform the shredding.
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>
>
|||The targetnamespace indicates that the elements need to belong to the
namespace to be matched by the schema definitions.
Best regards
Michael
"Illustris" <Illustris@.discussions.microsoft.com> wrote in message
news:1EC9D995-DADE-4321-B9C4-AC8A55E07E51@.microsoft.com...[vbcol=seagreen]
> Hello - found the issue - apparently, the:
> targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAxapta30_2.xsd"
> needs to be removed from the <xs:schema> tag.
> Now, can anyone tell me why? I'm not an XML expert but learning as much as
> I
> can...
> AB
> "Michael Rys [MSFT]" wrote:
sql

Importing Text File into SQL Server Problem

Hello,
I am trying to load a text file into SQL Server but the text file seems to be in an unsual format that SQL Server is having a problem reading. I have tried the various options for delimited and fixed file formats.
Any ideas would be appreciated.

Sample of the file:
Dn DCHB
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 200508082345079999 BANNER PAGE
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 420
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 425
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 440
What you are getting is called character conversion, try the link below to use DTS to move the file. Hope this helps.
http://www.sqldts.comsql

Importing Table Data Into an XML Column

I've been reading through BOL and various forms and am even more confused now as to what the best option is for what I need to accomplish...here's the scenario.

We have a staging table comprised of the following columns:

CN_CUST_KEY

ITM_SUF_NO

Model

Serial

SaleYear

SaleDate

prd_itm_no

LastName

MiddleName

FirstName

StreetAddress

...etc.

We need to load the data into a table with a similar structure, except that the first seven columns will be inserted into a column called CustomColumns, which has an XML datatype. The other columns will be inserted into their correlating columns.

I've already created the XSD and bound it to the XML column in my table:

Code Snippet

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="CustomColumns">

<xsd:complexType>

<xsd:complexContent>

<xsd:restriction base="xsd:anyType">

<xsd:sequence>

<xsd:element name="CN_CUST_KEY">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="ITM_SUF_NO">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="Model">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="Serial">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="SaleYear">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="SaleDate">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="prd_itm_no">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

</< FONT>xsd:sequence>

</< FONT>xsd:restriction>

</< FONT>xsd:complexContent>

</< FONT>xsd:complexType>

</< FONT>xsd:element>

</< FONT>xsd:schema>

So at the end of the day, the contents of any onw row's CustomColumns content would resemble:

Code Snippet

<CustomColumns>

<Lender></Lender>

<Dealership></Dealership>

<PayoffDate></PayoffDate>

<ConsentSigFlag></ConsentSigFlag>

<ConsentDateFlag></ConsentDateFlag>

<ConsentLenderFlag></ConsentLenderFlag>

<ContactCRAFalg></ContactCRAFalg>

<OwnerSigFlag></OwnerSigFlag>

<OwnerCompanyFlag></OwnerCompanyFlag>

<CertificateNo>QA095407</CertificateNo>

<AgentName>COLONIAL HARLEY DAVIDSON</AgentName>

<InsEFfDate>07/08/2006</InsEFfDate>

<HouseHoldClmQty>0</HouseHoldClmQty>

<HouseHoldClmSeq>0</HouseHoldClmSeq>

</CustomColumns>

...except of course with the appropriate column names.

One of the requirements of the process is that we're able to pop it into an SSIS pacakge for repeatable execution and modification whenever there is a new set of data points.

If anyone can provide any guidance, it would be greatly appreciated.

Thanks!!

I think I'm close....

Here's the query I ran:

Code Snippet

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers FOR XML PATH, ELEMENTS, ROOT('CustomColumns'), TYPE)

FROM TblClass_Customers;

...and here are the results:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s):CN_CUST_KEY where element 'row' was specified. Location: /*:CustomColumns[1]/*:row[1]

importing chinese text

Hello,
I have some multibyte characters and I want to put them into table fields.
how I can do this? Just storing them will result in a ? when reading /
showing them.
thanks & regards
MarkI'm doing similar thing without any problems.

My Chinese characters are in a text file, I used DTS to import them
into my SQL DB, they are shown on web pages perfectly.

How did you read/show them?

SL at http://www.source4book.com