How to import a XSD and XML file into a SQL table

I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
Karen
Download SQLXML 3.0
(http://www.microsoft.com/downloads/d...DisplayLang=en)
and read the documentation on the XML Bulk Load component. It's a COM
component you can call from a script (such as a .vbs) that will import data
from an XML file into a SQL Server database based on the mappings defined in
a schema.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegr oups.com...
I have generated the output of a SQL query against Northwind in the
form of the XSD and the XML file for a query using the following query:
For generating the XML output:
SELECT * FROM Customers
FOR XML AUTO
Similarly, I use the following query to generate a XSD output of the
above query from the Northwind database:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
Now I have the dump of the customers table in a XSD and XML format I
want to import the data in the resulting table into another database
using DTS or whatever to interpre the .XSD and the .XML files.
Please share the code to import a arbitrary .XML and .XSD file into a
SQL table and the meta data SQL must determine dynamically to create a
table in the target database.
Thanks
Karen
|||http://msdn.microsoft.com/library/de...exchsqlxml.asp
Download the sample code, project for this article as well as reading it...
One of the sample projects shows the nearly exaclty the code you inquired
about...
|||Just a clarification: XMLDATA does not generate an XSD but an XDR file. You
have to use a mid-tier tool that converts the XDR into XSD if you really
need an XSD (or use SQL Server 2005 where you can specify XMLSCHEMA to get
an XSD).
Best regards
Michael
<karenmiddleol@.yahoo.com> wrote in message
news:1116579889.377764.305650@.g44g2000cwa.googlegr oups.com...
>I have generated the output of a SQL query against Northwind in the
> form of the XSD and the XML file for a query using the following query:
> For generating the XML output:
> SELECT * FROM Customers
> FOR XML AUTO
> Similarly, I use the following query to generate a XSD output of the
> above query from the Northwind database:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
>
> Now I have the dump of the customers table in a XSD and XML format I
> want to import the data in the resulting table into another database
> using DTS or whatever to interpre the .XSD and the .XML files.
> Please share the code to import a arbitrary .XML and .XSD file into a
> SQL table and the meta data SQL must determine dynamically to create a
> table in the target database.
> Thanks
> Karen
>
|||Anyone knows how to dump the result of the XML AUTO query to an XML file?
I have a query and used bcp to dump it and i named it with an XML extension. If the result dump is very short like 3 records only. It is displaying OK when I try to opien it in IE Browser. But when the result of my BCP dump is big.. it is producing an error. The thing is, If I executr my query directly over the Browser (using HTTP with my database tied up to a Virtual Directory in IIS), It is displaying all the right results.
Can anyone help me on this? I just need to dump the XML result into a file so it can be archived and viewed.
Thanks,
Jeff|||Programmatically, you should use ADO or ADO.Net to write the FOR XML result
into the file stream. Alternatively, you can use the HTTP access through the
virtual directory and use view source, save as to save it...
Best regards
Michael
"pongaski" <pongaski.1pudhz@.mail.mcse.ms> wrote in message
news:pongaski.1pudhz@.mail.mcse.ms...
> Anyone knows how to dump the result of the XML AUTO query to an XML
> file?
> I have a query and used bcp to dump it and i named it with an XML
> extension. If the result dump is very short like 3 records only. It
> is displaying OK when I try to opien it in IE Browser. But when the
> result of my BCP dump is big.. it is producing an error. The thing is,
> If I executr my query directly over the Browser (using HTTP with my
> database tied up to a Virtual Directory in IIS), It is displaying all
> the right results.
> Can anyone help me on this? I just need to dump the XML result into a
> file so it can be archived and viewed.
> Thanks,
> Jeff
>
> --
> pongaski
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1626243.html
>
|||"Michael Rys [MSFT]" wrote:

> Programmatically, you should use ADO or ADO.Net to write the FOR XML result
> into the file stream. Alternatively, you can use the HTTP access through the
> virtual directory and use view source, save as to save it...
> Best regards
> Michael
It is not true. Lower I write stored proc wich write result "FOR XML" query
to txt file.
CREATE PROCEDURE usp_SQLtoXMLfile (
@.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
@.FileNameInput varchar(255), --File name with full path
@.headInput nvarchar(4000), --Prefix expression in result file
@.sufixInput nvarchar(4000) --postfix expression in result file
) AS
DECLARE @.FileName varchar(255)--\
DECLARE @.Text1 nvarchar(4000)--|
DECLARE @.FS int --|
DECLARE @.OLEResult int -- \
DECLARE @.FileID int -- /The bloc variables for work with files
DECLARE @.hr int--|
DECLARE @.source varchar(30)--|
DECLARE @.desc varchar (200)--/
DECLARE @.text NVARCHAR(4000)--bufer only
declare @.cur_edit cursor
declare @._kfvalue sql_variant--Return value
DECLARE @.sqlXmlQuerry NVARCHAR(4000)--Variable for query
BEGIN
EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
OUTPUT--create object for work with file system
IF @.OLEResult <> 0 --chek errors
BEGIN
PRINT 'Scripting.FileSystemObject'
GOTO Error_Handler
END
SET @.FileName=@.FileNameInput
execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
@.FileName --Create text file
IF @.OLEResult <> 0
BEGIN
PRINT 'CreateTextFile'
GOTO Error_Handler
END
-----
IF LEN(@.headInput)>0 --Check prefix empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
--Write to text file prefix
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
-----
SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
Server};SERVER='+@.@.SERVERNAME+''','''+@.sqlXmlQuerr y+''') '--Adapt query for
cursor
SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+' open
@.cur_edit ' --Add to query string declaration and opening cursor
--PRINT @.sqlXmlQuerry --for debug
exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
sql_variant', @.cur_edit output, @._kfvalue --Execute the string
FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
WHILE (@.@.fetch_status = 0)
BEGIN
-----
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add to
text file value from @.text
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
FETCH NEXT FROM @.cur_edit INTO @.text
END
CLOSE @.cur_edit
DEALLOCATE @.cur_edit
-----
IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
BEGIN
execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
--Write postfix expression to text file
IF @.OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
END
-----goto Done
Error_Handler:
PRINT '*** ERROR ***'
EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description = @.desc
Done:
EXECUTE @.OLEResult = sp_OADestroy @.FileID
EXECUTE @.OLEResult = sp_OADestroy @.FS
END
GO
PS Sorry for my english...
|||You can use sp_OA stored procs, but it is not something that I recommend
(note that I say "should" and not "it is the only way" :-)).
Thanks for the posting though and no worries about the English. TSQL crosses
language-boundaries :-)
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:C525C398-29B0-4FF1-A494-29D6B61CD66E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
> It is not true. Lower I write stored proc wich write result "FOR XML"
> query
> to txt file.
> CREATE PROCEDURE usp_SQLtoXMLfile (
> @.sqlXmlQuerryInput NVARCHAR(4000), --Query text with FOR XML
> @.FileNameInput varchar(255), --File name with full path
> @.headInput nvarchar(4000), --Prefix expression in result file
> @.sufixInput nvarchar(4000) --postfix expression in result file
> ) AS
> DECLARE @.FileName varchar(255) --\
> DECLARE @.Text1 nvarchar(4000) --|
> DECLARE @.FS int --|
> DECLARE @.OLEResult int -- \
> DECLARE @.FileID int -- /The bloc variables for work with files
> DECLARE @.hr int --|
> DECLARE @.source varchar(30) --|
> DECLARE @.desc varchar (200) --/
> DECLARE @.text NVARCHAR(4000) --bufer only
> declare @.cur_edit cursor
> declare @._kfvalue sql_variant --Return value
> DECLARE @.sqlXmlQuerry NVARCHAR(4000) --Variable for query
> BEGIN
> ----
> EXECUTE @.OLEResult = sp_OACreate 'Scripting.FileSystemObject', @.FS
> OUTPUT --create object for work with file system
> IF @.OLEResult <> 0 --chek errors
> BEGIN
> PRINT 'Scripting.FileSystemObject'
> GOTO Error_Handler
> END
> SET @.FileName=@.FileNameInput
> execute @.OLEResult = sp_OAMethod @.FS,'CreateTextFile',@.FileID OUTPUT,
> @.FileName --Create text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'CreateTextFile'
> GOTO Error_Handler
> END
> -----
> IF LEN(@.headInput)>0 --Check prefix empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'WriteLine', NULL, @.headInput
> --Write to text file prefix
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> -----
> SET @.sqlXmlQuerry=@.sqlXmlQuerryInput --Query text
> SET @.sqlXmlQuerry=' SELECT * FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL
> Server};SERVER='+@.@.SERVERNAME+''','''+@.sqlXmlQuerr y+''') ' --Adapt query
> for
> cursor
> SET @.sqlXmlQuerry=' set @.cur_edit = CURSOR STATIC FOR '+@.sqlXmlQuerry+'
> open
> @.cur_edit ' --Add to query string declaration and opening cursor
> --PRINT @.sqlXmlQuerry --for debug
> exec sp_executesql @.sqlXmlQuerry, N'@.cur_edit cursor output, @._kfvalue
> sql_variant', @.cur_edit output, @._kfvalue --Execute the string
> FETCH NEXT FROM @.cur_edit INTO @.text --Return from cursor
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -----
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.text --Add
> to
> text file value from @.text
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> FETCH NEXT FROM @.cur_edit INTO @.text
> END
> CLOSE @.cur_edit
> DEALLOCATE @.cur_edit
> -----
> IF LEN(@.sufixInput)>0 --If variable with postfix expression not empty
> BEGIN
> execute @.OLEResult = sp_OAMethod @.FileID, 'Write', NULL, @.sufixInput
> --Write postfix expression to text file
> IF @.OLEResult <> 0
> BEGIN
> PRINT 'WriteLine'
> GOTO Error_Handler
> END
> END
> -----goto
> Done
> Error_Handler:
> PRINT '*** ERROR ***'
> EXEC @.hr = sp_OAGetErrorInfo null, @.source OUT, @.desc OUT
> SELECT hr = CONVERT (binary(4), @.hr), source = @.source, description =
> @.desc
> Done:
> EXECUTE @.OLEResult = sp_OADestroy @.FileID
> EXECUTE @.OLEResult = sp_OADestroy @.FS
> END
> GO
>
> PS Sorry for my english...
>
|||"Michael Rys [MSFT]" wrote:
> You can use sp_OA stored procs, but it is not something that I recommend
> (note that I say "should" and not "it is the only way" :-)).

> Thanks for the posting though and no worries about the English. TSQL crosses
> language-boundaries :-)
> Michael
You right of all points of course ;)
Why you not recommend use sp_OA stored procs? I know, it is not TSQL in pure
form, but it is one of ways to to solve the problem... Can you describe a
problems wich may appear?
|||It is very easy to shoot yourself into the foot and corrupt the server if
you are not careful.
And it has many not so clear interactions (thread safety, memory etc) that
you need to be aware of.
So this is clearly black-belt territory.
Finally, I rather have us provide solutions to these problems instead of
letting us say: "Oh, but sp_OA stored procs can do it" :-).
Best regards
Michael
"Serbat_Ivan" <SerbatIvan@.discussions.microsoft.com> wrote in message
news:E3855E10-23B3-43EA-A416-B743C71E2E0E@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> You right of all points of course ;)
> Why you not recommend use sp_OA stored procs? I know, it is not TSQL in
> pure
> form, but it is one of ways to to solve the problem... Can you describe a
> problems wich may appear?