Need converting a table to XML

Question

Need converting a table to XML

stephenmbell

SSC Eights!

Points: 926
More actions
November 7, 2012 at 11:42 am

#98737

I am very far from an expert when it comes to working with xml - but I have some exposure to it. I was recently given a project to take some data we have in a spreadsheet, get it into SQL server, create a new table for it (as per our new vendors spec) and export it do a certain format XML. So I am at a point now where I have all of the data in the database, moved into the new table. I am struggling getting it into the proper XML format.
Our vendor gave us an xml format to get the data into but I just cannot seem to get it to work.
In the xml spec that I have I see - urn:schemas-microsoft-com:rowset - and I did a little bit of digging on this. I can use the "FOR XML AUTO TYPE(urn:schemas-microsoft-com:rowset)" to put it in this type - but the data doesn't look how the the company is expecting it.
I even tried to create a new xml schema using the xml file that was provided to me but I couldn't get this to work either. I am sure this is because of me and nothing else.
How should I go about accomplishing this?
Thanks in advance
sb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

stephenmbell (11/7/2012)
I am very far from an expert when it comes to working with xml - but I have some exposure to it. I was recently given a project to take some data we have in a spreadsheet, get it into SQL server, create a new table for it (as per our new vendors spec) and export it do a certain format XML. So I am at a point now where I have all of the data in the database, moved into the new table. I am struggling getting it into the proper XML format.
Our vendor gave us an xml format to get the data into but I just cannot seem to get it to work.
In the xml spec that I have I see - urn:schemas-microsoft-com:rowset - and I did a little bit of digging on this. I can use the "FOR XML AUTO TYPE(urn:schemas-microsoft-com:rowset)" to put it in this type - but the data doesn't look how the the company is expecting it.
I even tried to create a new xml schema using the xml file that was provided to me but I couldn't get this to work either. I am sure this is because of me and nothing else.
How should I go about accomplishing this?
Thanks in advance
sb

From what you posted it is impossible to provide much help. We don't know what the table or the desired xml format looks like. Take a look at the first link in my signature about best practices when posting quetions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

stephenmbell SSC Eights! Points: 926 More actions · Answer 2

My apologies:

Here is the table that I have created, and I have populated with data--

CREATE TABLE [dbo].[MyTable](

[Candidate First Name] [nvarchar](50) NULL,

[Candidate Middle Name] [nvarchar](25) NULL,

[Candidate Last Name] [nvarchar](50) NULL,

[Candidate Name Prefix] [nvarchar](50) NULL,

[Candidate Name Suffix] [nvarchar](50) NULL,

[Candidate Email] [nvarchar](100) NOT NULL,

[Candidate Address Line 1] [nvarchar](50) NULL,

[Candidate Address Line 2] [nvarchar](50) NULL,

[Candidate City] [nvarchar](50) NULL,

[Candidate State/Province/Region] [nvarchar](100) NOT NULL,

[Candidate Postal Code] [nvarchar](50) NOT NULL,

[Candidate Country] [nvarchar](5) NOT NULL,

[Candidate Primary Phone] [nvarchar](50) NOT NULL,

[Candidate Secondary Phone] [nvarchar](50) NULL,

[Primary Contact Method] [int] NULL,

[Enter Date] [datetime] NULL,

[Original Source] [nvarchar](100) NULL,

[Desired Career Level] [int] NULL,

[Professional Certifications] [nvarchar](200) NULL,

[Candidate's Work Authorization] [nvarchar](3) NULL,

[Collegiate Majors] [nvarchar](50) NULL,

[Current Job Type] [int] NULL,

[Date Available] [datetime] NULL,

[Degree Level] [int] NULL,

[Desired Job Type] [int] NULL,

[Candidate's Disposition] [nvarchar](100) NULL,

[Password] [nvarchar](60) NULL,

[Willing to Relocate?] [nvarchar](10) NULL,

[Relocation Preferences] [nvarchar](255) NULL,

[Resume Body] [text] NULL,

[Salary Requirement] [nvarchar](255) NULL,

[Security Clearance?] [bit] NULL,

[Skill Set] [nvarchar](100) NULL,

[Contact Notes] [nvarchar](500) NULL,

[Comment] [nvarchar](500) NULL,

[Additional Countries] [nvarchar](75) NULL,

[Years of Experience] [int] NULL,

[Veteran Status] [varchar](75) NULL,

[Ethnicity Code] [int] NULL,

[Gender] [varchar](3) NULL,

[Disabled Status] [varchar](3) NULL,

[Job ID] [int] NULL,

[Stage Seq No] [int] NULL,

[Contact Info Custom Field 1 - 10] [varchar](500) NULL,

[Resume Custom Field 1- 10] [varchar](500) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And the xml file that they sent me is:

<xml version="1.0" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"

xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"

xmlns:rs="urn:schemas-microsoft-com:rowset"

xmlns:z="#RowsetSchema">

<s:Schema id="RowsetSchema">

<s:ElementType name="row" content="eltOnly" rs:updatable="true">

<s:AttributeType name="action" rs:number="3" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Openhire"

rs:basetable="candidates" rs:basecolumn="action">

<s:datatype dt:type="ui1" dt:maxLength="1" rs:precision="3" rs:fixedlength="true"/>

</s:AttributeType>

<s:AttributeType name="column1" rs:number="4" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Openhire"

rs:basetable="candidates" rs:basecolumn="column1">

<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="100"/>

</s:AttributeType>

<s:AttributeType name="column2" rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="Openhire"

rs:basetable="candidates" rs:basecolumn="column2">