I thought this should be simple but it does not seem to work. I was
trying to use FOR XML to generate XML data and XSD and
SQLXMLBULKLOADLib to import the data. In actual implementation the
data will be generated on one SQL server instance and then imported -
creating a new table on another. For test purposes I was extracting
data from an existing table, modifying it and then trying to import it
back to the same table. However, the XSD I get from:

SELECT USERID + '99' AS USERID, UserGrp AS UserGrp, BOS AS BOS,
MultiBOS AS MultiBOS, BOSacct AS BOSacct,
COAVisibility as COAVisibility, Organization AS Organization, Office
AS Office, Location AS Location, Title AS Title,
LName AS LName, FName AS FName, Phone AS Phone, EMAIL AS EMAIL, Remark
AS Remark, ActiveUser AS ActiveUser,
UserType AS UserType, WinUserID AS WinUserID, RptVisibility AS
RptVisibility, OPAGY AS OPAGY, OPAGYacct AS OPAGYacct,
Valid_Sponsor AS Valid_Sponsor, CONVERT(CHAR(10),Valid_date,101) AS
Valid_date, History, DASC_Group, DASC_Level
FROM tblUsers WHERE USERID LIKE 'zzz%' FOR XML AUTO, XMLSCHEMA

This does not Give me errors when I try to import the data back to the
table using SQLXMLBULKLOADLib, it just doesn't create any new data.

Shouldn't this work?

Note that the XSD generated by FOR XML does not create the annotations to
perform the mapping. It only creates a validating schema.

You would have to provide the mapping in the XSD afterwards.

If you find this an important or useful feature to add, I encourage you to
use our feedback site at http://connect.microsoft.com/sqlserver

Best regards
Michael

"EPK" <eileen.kelly (AT) ngc (DOT) com> wrote

On May 14, 4:00 pm, "Michael Rys [MSFT]" <m... (AT) online (DOT) microsoft.com>
wrote:
Mapping? I'm not following you here I thought the mapping was
<xsd:element name="USERID"> part of the statement. What else do I
need? Do I have to create it manually? (SQL Server won't do it for
me? )

FOR XML only creates the XML Schema for the data it returns. It does not
provide you with a schema that can be used to map the data back against the
database. If you want to use the SQLXML Bulkload functionality, you need to
provide mapping annotations to the XML Schema. And yes, FOR XML currently
does not create them automatically.

Best regards
Michael

PS: I recommend to look at the relevant annotated XSD schema chapter in BOL
(e.g.,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlxml49/html/eecd0d5f-d3dd-4d20-9586-19820410ad47.htm)

"EPK" <eileen.kelly (AT) ngc (DOT) com> wrote

I've found a similar problem, The guys at http://blog.liquid-technologies.com/ helped me out though, just ask a question on their blog. Theres a post about Importing too.