XML Mapping Problem

Question

XML Mapping Problem

dnlandes

Grasshopper

Points: 15
More actions
September 30, 2008 at 2:38 pm

#213509

I used this: http://support.microsoft.com/kb/316005 as a template for importing my xml data into a sql table however I have run into a problem. The XML data:
The Map
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
With this data and this map I get all the info in a table. Yay! But It puts OfficeTradingAs on one row then moves to the next row and puts OfficeOfficePhone on the next row and OfficeCityName, and OfficeCityState, etc. How can I modify this or what do I need to do to get all the info on one line? Any help would be appreciated.

Viewing 8 posts - 1 through 8 (of 8 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

This forum's security filters will usually eat any XML imbeded in a post, so just attach it to a reply as an XML file.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dnlandes Grasshopper Points: 15 More actions · Answer 2

Boo! Here is the xml file....well a short version of it and the mapping file.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

dnlandes (9/30/2008)
With this data and this map I get all the info in a table. Yay! But It puts OfficeTradingAs on one row then moves to the next row and puts OfficeOfficePhone on the next row and OfficeCityName, and OfficeCityState, etc. How can I modify this or what do I need to do to get all the info on one line?

Could you please post the code that you are using to put the XML into the table, also the schema of the table (in CREATE TABLE format) would be very helpful as well.

Thanks,

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dnlandes Grasshopper Points: 15 More actions · Answer 4

USE Office

CREATE TABLE Office1 (

OfficeTradingAs NVARCHAR(50),

OfficeOfficePhone NVARCHAR(20),

OfficeCityName NVARCHAR(20),

OfficeState NVARCHAR(20),

OfficeID NVARCHAR(20))

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

This is the only thing that I can think of that might work (see attached).

I am not too familiar with this approach of using the XML bulk loader and annotated XML schemas. I know that this could be done by bringing the XML file in as an XML datatype column or variable and then using the XQuery functions to shred it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dnlandes Grasshopper Points: 15 More actions · Answer 6

I got an error when I tried that. I'll look into the other option you mentioned too.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

OK, let us know how it works out.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]