I have an XML file that is hosted on a mainframe web server. I need to read
the file via a URL and import this into my SQL database.
I'm fairly new to SQL 2005 but this seems to be a simple task. I've looked
at using the Import Export wizard but I don't see a simpl way to do this.
I've tried selecting "Flat File" as my data source but this does not seem to
be able to read XML. I've tried the SQLSML data sources but I don't think
this will work if the web server is not IIS.
Does anyone have any ideas?"Bryan" <Bryan@.discussions.microsoft.com> wrote in message
news:37B66D17-FE91-4CFD-B502-DB80BD2B8360@.microsoft.com...
>I have an XML file that is hosted on a mainframe web server. I need to
>read
> the file via a URL and import this into my SQL database.
> I'm fairly new to SQL 2005 but this seems to be a simple task. I've
> looked
> at using the Import Export wizard but I don't see a simpl way to do this.
> I've tried selecting "Flat File" as my data source but this does not seem
> to
> be able to read XML. I've tried the SQLSML data sources but I don't think
> this will work if the web server is not IIS.
> Does anyone have any ideas?
Write a SQLCLR function in C# or VB to retrieve the data from the web
server.
Showing posts with label fairly. Show all posts
Showing posts with label fairly. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Importing TXT files problems
Hello,
I'm fairly new to SQL and hopefully this is a simple question to answer.
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file where the fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is
32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002 etc. I don't want everything as characters or Col001 etc. Some columns would be
INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc., FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JB
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
"JonBlack" <anonymous@.discussions.microsoft.com> wrote in message
news:AE462262-F98D-40A1-BD57-6E2066BE2A6D@.microsoft.com...
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having
trouble making it work correctly. It is a ASCII text file where the fields
vary by the number of characters. This can be 2 characters up to 40 (STATE
would be 2 characters, CITY is 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want
the field breaks to be. Then it imports everything as Characters with column
headers of Col001, Col002 etc. I don't want everything as characters or
Col001 etc. Some columns would be INT, NUMERIC(x,x), etc. but everytime I
change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB
|||Hi Steve,
Thanks for your reply. I wouldn't mind importing it into EXCEL and saving it in a better format. Unfortunately I have over 100,000 records, which rules EXCEL out. I think BULK IMPORT and BCP can't import files based on character widths but could use tabs
as you suggested. Using VB to write an import script could be an option though.
Thanks again,
JB
-- Steve Z wrote: --
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
|||Is it a column a fix length or delimited by coma ?
JonBlack wrote:
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file where the fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY i
s 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002 etc. I don't want everything as characters or Col001 etc. Some columns would b
e INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB
|||The columns are fixed length. For example Column1 is 5 characters long, Column2 is 2 characters long, ColumnC is 30 characters long, ColumnD is 10 characters long, etc. Also, some of the columns are numeric and integer as well but are represented by chara
cters until imported.
Thanks,
JB
I'm fairly new to SQL and hopefully this is a simple question to answer.
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file where the fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is
32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002 etc. I don't want everything as characters or Col001 etc. Some columns would be
INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc., FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JB
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
"JonBlack" <anonymous@.discussions.microsoft.com> wrote in message
news:AE462262-F98D-40A1-BD57-6E2066BE2A6D@.microsoft.com...
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having
trouble making it work correctly. It is a ASCII text file where the fields
vary by the number of characters. This can be 2 characters up to 40 (STATE
would be 2 characters, CITY is 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want
the field breaks to be. Then it imports everything as Characters with column
headers of Col001, Col002 etc. I don't want everything as characters or
Col001 etc. Some columns would be INT, NUMERIC(x,x), etc. but everytime I
change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB
|||Hi Steve,
Thanks for your reply. I wouldn't mind importing it into EXCEL and saving it in a better format. Unfortunately I have over 100,000 records, which rules EXCEL out. I think BULK IMPORT and BCP can't import files based on character widths but could use tabs
as you suggested. Using VB to write an import script could be an option though.
Thanks again,
JB
-- Steve Z wrote: --
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
|||Is it a column a fix length or delimited by coma ?
JonBlack wrote:
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file where the fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY i
s 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002 etc. I don't want everything as characters or Col001 etc. Some columns would b
e INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB
|||The columns are fixed length. For example Column1 is 5 characters long, Column2 is 2 characters long, ColumnC is 30 characters long, ColumnD is 10 characters long, etc. Also, some of the columns are numeric and integer as well but are represented by chara
cters until imported.
Thanks,
JB
Importing TXT files problems
Hello,
I'm fairly new to SQL and hopefully this is a simple question to answer.
I've been trying to import a TXT file into an SQL database and I'm having tr
ouble making it work correctly. It is a ASCII text file where the fields var
y by the number of characters. This can be 2 characters up to 40 (STATE woul
d be 2 characters, CITY is
32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the
field breaks to be. Then it imports everything as Characters with column hea
ders of Col001, Col002 etc. I don't want everything as characters or Col001
etc. Some columns would be
INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't
import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the f
ield lengths, data type etc., FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JBWe have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
"JonBlack" <anonymous@.discussions.microsoft.com> wrote in message
news:AE462262-F98D-40A1-BD57-6E2066BE2A6D@.microsoft.com...
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having
trouble making it work correctly. It is a ASCII text file where the fields
vary by the number of characters. This can be 2 characters up to 40 (STATE
would be 2 characters, CITY is 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want
the field breaks to be. Then it imports everything as Characters with column
headers of Col001, Col002 etc. I don't want everything as characters or
Col001 etc. Some columns would be INT, NUMERIC(x,x), etc. but everytime I
change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB|||Hi Steve,
Thanks for your reply. I wouldn't mind importing it into EXCEL and saving it
in a better format. Unfortunately I have over 100,000 records, which rules
EXCEL out. I think BULK IMPORT and BCP can't import files based on character
widths but could use tabs
as you suggested. Using VB to write an import script could be an option thou
gh.
Thanks again,
JB
-- Steve Z wrote: --
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...|||Is it a column a fix length or delimited by coma ?
JonBlack wrote:
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having trouble
making it work correctly. It is a ASCII text file where the fields vary by the numbe
r of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CIT
Y i
s 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want the field
breaks to be. Then it imports everything as Characters with column headers of Col001
, Col002 etc. I don't want everything as characters or Col001 etc. Some columns woul
d b
e INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the dat
a correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB|||The columns are fixed length. For example Column1 is 5 characters long, Colu
mn2 is 2 characters long, ColumnC is 30 characters long, ColumnD is 10 chara
cters long, etc. Also, some of the columns are numeric and integer as well b
ut are represented by chara
cters until imported.
Thanks,
JB
I'm fairly new to SQL and hopefully this is a simple question to answer.
I've been trying to import a TXT file into an SQL database and I'm having tr
ouble making it work correctly. It is a ASCII text file where the fields var
y by the number of characters. This can be 2 characters up to 40 (STATE woul
d be 2 characters, CITY is
32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the
field breaks to be. Then it imports everything as Characters with column hea
ders of Col001, Col002 etc. I don't want everything as characters or Col001
etc. Some columns would be
INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't
import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the f
ield lengths, data type etc., FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JBWe have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
"JonBlack" <anonymous@.discussions.microsoft.com> wrote in message
news:AE462262-F98D-40A1-BD57-6E2066BE2A6D@.microsoft.com...
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having
trouble making it work correctly. It is a ASCII text file where the fields
vary by the number of characters. This can be 2 characters up to 40 (STATE
would be 2 characters, CITY is 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want
the field breaks to be. Then it imports everything as Characters with column
headers of Col001, Col002 etc. I don't want everything as characters or
Col001 etc. Some columns would be INT, NUMERIC(x,x), etc. but everytime I
change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB|||Hi Steve,
Thanks for your reply. I wouldn't mind importing it into EXCEL and saving it
in a better format. Unfortunately I have over 100,000 records, which rules
EXCEL out. I think BULK IMPORT and BCP can't import files based on character
widths but could use tabs
as you suggested. Using VB to write an import script could be an option thou
gh.
Thanks again,
JB
-- Steve Z wrote: --
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...|||Is it a column a fix length or delimited by coma ?
JonBlack wrote:
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having trouble
making it work correctly. It is a ASCII text file where the fields vary by the numbe
r of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CIT
Y i
s 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want the field
breaks to be. Then it imports everything as Characters with column headers of Col001
, Col002 etc. I don't want everything as characters or Col001 etc. Some columns woul
d b
e INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the dat
a correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB|||The columns are fixed length. For example Column1 is 5 characters long, Colu
mn2 is 2 characters long, ColumnC is 30 characters long, ColumnD is 10 chara
cters long, etc. Also, some of the columns are numeric and integer as well b
ut are represented by chara
cters until imported.
Thanks,
JB
Friday, March 9, 2012
Importing Data Into XML Column - UPDATED
Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:
<xsd
chema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="CustomColumns">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsdequence>
<xsd:element name="CN_CUST_KEY" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:float" />
</xsdimpleType>
</xsd:element>
<xsd:element name="ITM_SUF_NO" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:float" />
</xsdimpleType>
</xsd:element>
<xsd:element name="Model" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdtring" />
</xsdimpleType>
</xsd:element>
<xsd:element name="Serial" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdtring" />
</xsdimpleType>
</xsd:element>
<xsd:element name="SaleYear" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:int" />
</xsdimpleType>
</xsd:element>
<xsd:element name="SaleDate" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd
ateTime" />
</xsdimpleType>
</xsd:element>
<xsd:element name="prd_itm_no" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:int" />
</xsdimpleType>
</xsd:element>
</xsdequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsdchema>
This is the SQL query I'm running against the source table:
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 is the error I'm getting:
Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]
From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...
Kind of at a loss here...
This is what the contents of the target xml column should look like:
<CustomColumns>
<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>
<ITM_SUF_NO>45</ITM_SUF_NO>
<Model>alpha</Model>
<Serial>djdjxh3455skx</Serial>
<SaleYear>2005</SaleYear>
<SaleDate>10/25</SaleDate>
<prd_itm_no>df345f</prd_itm_no>
</CustomColumns>
One thing I did find...if I just run the portion of the query that selects the columns for my XML column, the query runs, but doesn't give the expected results. It concatenates everything into one long string...
<CustomColumns><row><CN_CUST_KEY>1000422</CN_CUST_KEY><ITM_SUF_NO>9</ITM_SUF_NO><Model>11073954200</Model><SaleYear>2003</SaleYear><SaleDate>2003-08-23 00:00:00</SaleDate><prd_itm_no>73954</prd_itm_no></row><row><CN_CUST_KEY>1000812</CN_CUST_KEY><ITM_SUF_NO>13</ITM_SUF_NO><Model>11063932101</Model><Serial>MM2610444</Serial><SaleYear>2002</SaleYear><SaleDate>2002-08-26 00:00:00</SaleDate><prd_itm_no>63932</prd_itm_no></row><row><CN_CUST_KEY>1001610</CN_CUST_KEY><ITM_SUF_NO>14</ITM_SUF_NO><Model>11062952100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-03 00:00:00</SaleDate><prd_itm_no>62952</prd_itm_no></row><row><CN_CUST_KEY>1004682</CN_CUST_KEY><ITM_SUF_NO>15</ITM_SUF_NO><Model>11072932100</Model><Serial>ML140325</Serial><SaleYear>2001</SaleYear><SaleDate>2001-04-21 00:00:00</SaleDate><prd_itm_no>72932</prd_itm_no></row><row><CN_CUST_KEY>1004867</CN_CUST_KEY><ITM_SUF_NO>12</ITM_SUF_NO><Model>11073952200</Model><SaleYear>2003</SaleYear><SaleDate>2003-04-08 00:00:00</SaleDate><prd_itm_no>73952</prd_itm_no></row><row><CN_CUST_KEY>1005117</CN_CUST_KEY><ITM_SUF_NO>7</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-18 00:00:00</SaleDate><prd_itm_no>72972</prd_itm_no></row><row><CN_CUST_KEY>1005320</CN_CUST_KEY><ITM_SUF_NO>27</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2001</SaleYear><SaleDate>2001-08-28 -07-01 00:00:00</SaleDate><prd_itm_no>73942</prd_itm_no></row><row><CN_CUST_KEY>5806620</CN_CUST_KEY><ITM_SUF_NO>2</ITM_SUF_NO><Model>11062952100</Model><Serial>ML4334523</Serial><SaleYear>2002</SaleYear><SaleDate>2002-02-17
It also is inserting the <row> tag as opposed to actually creating an entirely new row. And lastly, the <CustomColumns> tag should encapsulate/wrap each record. When I run the select, every single row is contained within <CustomColumns>...
|||You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:
.....
FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID
FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)
FROM TblClass_Customers T2;
|||Thanks a million!!!
Do you think you could just real quick explain to me what it is that made your chnges do the trick?
I'm learning from this whole XML experience...
Thanks again!!
|||Phe:
The SELECT alone works fine, but when coupled with the INSERT it blows up:
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_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY
FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)
FROM TblClass_Customers2 T2
The validation error again:
Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]
I figured out hat the issue is, but not sure how to address it...
If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.
How would you tell the query to select the column/element even if there is no value or NULL in the source column?
I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...
|||In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.
If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.
|||
Phe:
Thanks for the reply....
I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:
Code Snippet
<CustomColumns xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<CN_CUST_KEY>1000422</CN_CUST_KEY>
<ITM_SUF_NO>9</ITM_SUF_NO>
<Model>11073954200</Model>
<Serial xsi:nil="true" />
<SaleYear>2003</SaleYear>
<SaleDate>2003-08-23 00:00:00</SaleDate>
<prd_itm_no>73954</prd_itm_no>
</CustomColumns>
Is there any other way to accomplish this without having to have the attribute and the header there? Or is that the only way? I'm going to assume even if the attribute is there, I would just need to modify my queries to account for that particular attribute...
I've never used XQUERY before, and I've been reading through BOL, but again, it's all Greek to me. How would you formulate a basic query against the XML above. For eample, I just want to select all rows where the <Serial> value is NULL (NIL)...?
Am I making sense...? I hope so!!
Thanks again...
|||You can check e.g.
Code Snippet
SELECT @.x.query(
'CustomColumns[Serial/@.xsi:nil = true()]'
);
|||Thanks Martin:
But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element
Last question I swear....!
So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?
For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:
Code Snippet
SELECT clmnt.query('
declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";
/CustomColumns/Serial
') as Result
FROM [CustomColumns].[Serial]
WHERE Serial = '140325'
I'm sure it's completely way off the mark, but if someone could give some insight that would be great.
Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...
Thanks for tolerating an XML noob...
Importing Data Into XML Column - UPDATED
Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:
<xsdchema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="CustomColumns">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsdequence>
<xsd:element name="CN_CUST_KEY" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:float" />
</xsdimpleType>
</xsd:element>
<xsd:element name="ITM_SUF_NO" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:float" />
</xsdimpleType>
</xsd:element>
<xsd:element name="Model" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdtring" />
</xsdimpleType>
</xsd:element>
<xsd:element name="Serial" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdtring" />
</xsdimpleType>
</xsd:element>
<xsd:element name="SaleYear" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:int" />
</xsdimpleType>
</xsd:element>
<xsd:element name="SaleDate" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsdateTime" />
</xsdimpleType>
</xsd:element>
<xsd:element name="prd_itm_no" nillable="true">
<xsdimpleType>
<xsd:restriction base="xsd:int" />
</xsdimpleType>
</xsd:element>
</xsdequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsdchema>
This is the SQL query I'm running against the source table:
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 is the error I'm getting:
Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]
From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...
Kind of at a loss here...
This is what the contents of the target xml column should look like:
<CustomColumns>
<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>
<ITM_SUF_NO>45</ITM_SUF_NO>
<Model>alpha</Model>
<Serial>djdjxh3455skx</Serial>
<SaleYear>2005</SaleYear>
<SaleDate>10/25</SaleDate>
<prd_itm_no>df345f</prd_itm_no>
</CustomColumns>
One thing I did find...if I just run the portion of the query that selects the columns for my XML column, the query runs, but doesn't give the expected results. It concatenates everything into one long string...
<CustomColumns><row><CN_CUST_KEY>1000422</CN_CUST_KEY><ITM_SUF_NO>9</ITM_SUF_NO><Model>11073954200</Model><SaleYear>2003</SaleYear><SaleDate>2003-08-23 00:00:00</SaleDate><prd_itm_no>73954</prd_itm_no></row><row><CN_CUST_KEY>1000812</CN_CUST_KEY><ITM_SUF_NO>13</ITM_SUF_NO><Model>11063932101</Model><Serial>MM2610444</Serial><SaleYear>2002</SaleYear><SaleDate>2002-08-26 00:00:00</SaleDate><prd_itm_no>63932</prd_itm_no></row><row><CN_CUST_KEY>1001610</CN_CUST_KEY><ITM_SUF_NO>14</ITM_SUF_NO><Model>11062952100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-03 00:00:00</SaleDate><prd_itm_no>62952</prd_itm_no></row><row><CN_CUST_KEY>1004682</CN_CUST_KEY><ITM_SUF_NO>15</ITM_SUF_NO><Model>11072932100</Model><Serial>ML140325</Serial><SaleYear>2001</SaleYear><SaleDate>2001-04-21 00:00:00</SaleDate><prd_itm_no>72932</prd_itm_no></row><row><CN_CUST_KEY>1004867</CN_CUST_KEY><ITM_SUF_NO>12</ITM_SUF_NO><Model>11073952200</Model><SaleYear>2003</SaleYear><SaleDate>2003-04-08 00:00:00</SaleDate><prd_itm_no>73952</prd_itm_no></row><row><CN_CUST_KEY>1005117</CN_CUST_KEY><ITM_SUF_NO>7</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-18 00:00:00</SaleDate><prd_itm_no>72972</prd_itm_no></row><row><CN_CUST_KEY>1005320</CN_CUST_KEY><ITM_SUF_NO>27</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2001</SaleYear><SaleDate>2001-08-28 -07-01 00:00:00</SaleDate><prd_itm_no>73942</prd_itm_no></row><row><CN_CUST_KEY>5806620</CN_CUST_KEY><ITM_SUF_NO>2</ITM_SUF_NO><Model>11062952100</Model><Serial>ML4334523</Serial><SaleYear>2002</SaleYear><SaleDate>2002-02-17
It also is inserting the <row> tag as opposed to actually creating an entirely new row. And lastly, the <CustomColumns> tag should encapsulate/wrap each record. When I run the select, every single row is contained within <CustomColumns>...
|||You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:
.....
FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID
FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)
FROM TblClass_Customers T2;
|||Thanks a million!!!
Do you think you could just real quick explain to me what it is that made your chnges do the trick?
I'm learning from this whole XML experience...
Thanks again!!
|||Phe:
The SELECT alone works fine, but when coupled with the INSERT it blows up:
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_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY
FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)
FROM TblClass_Customers2 T2
The validation error again:
Msg 6965, Level 16, State 1, Line 1
XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]
I figured out hat the issue is, but not sure how to address it...
If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.
How would you tell the query to select the column/element even if there is no value or NULL in the source column?
I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...
|||In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.
If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.
|||
Phe:
Thanks for the reply....
I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:
Code Snippet
<CustomColumns xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<CN_CUST_KEY>1000422</CN_CUST_KEY>
<ITM_SUF_NO>9</ITM_SUF_NO>
<Model>11073954200</Model>
<Serial xsi:nil="true" />
<SaleYear>2003</SaleYear>
<SaleDate>2003-08-23 00:00:00</SaleDate>
<prd_itm_no>73954</prd_itm_no>
</CustomColumns>
Is there any other way to accomplish this without having to have the attribute and the header there? Or is that the only way? I'm going to assume even if the attribute is there, I would just need to modify my queries to account for that particular attribute...
I've never used XQUERY before, and I've been reading through BOL, but again, it's all Greek to me. How would you formulate a basic query against the XML above. For eample, I just want to select all rows where the <Serial> value is NULL (NIL)...?
Am I making sense...? I hope so!!
Thanks again...
|||You can check e.g.
Code Snippet
SELECT @.x.query(
'CustomColumns[Serial/@.xsi:nil = true()]'
);
|||Thanks Martin:
But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element
Last question I swear....!
So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?
For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:
Code Snippet
SELECT clmnt.query('
declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";
/CustomColumns/Serial
') as Result
FROM [CustomColumns].[Serial]
WHERE Serial = '140325'
I'm sure it's completely way off the mark, but if someone could give some insight that would be great.
Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...
Thanks for tolerating an XML noob...
Subscribe to:
Posts (Atom)