XML Data Type... Format of XML Data

Question

Post reply

XML Data Type... Format of XML Data

mhilligas

Ten Centuries

Points: 1354
More actions
August 25, 2009 at 4:27 pm

#209031

I am working with the XML data type. I have noticed the systax in the xml of:
I am looking to generate syntax like:
0702157-01
I am looking to eliminate the = sign.
Is this the result of the xml schema?
Any help would be GREAT!
Regards
Michael J. Hilligas

Viewing 12 posts - 1 through 11 (of 11 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi Michael,

Short answer to your question

Is this the result of the xml schema?

: It depends.

There is a difference between an attribute (like SampleGroupID is an attribute of the item element in your first example) versus an element (like SampleGroupID in your second example):

Within a node you can have multiple elements.

Each element may have more than one (named) attribute. If the attribute is not named (having a separate name with a following = ), it becomes the value of the element, otherwise the text following the = represents the value of one attribute within the element imside a node. Look at it like a tree structure...

What's the reason for eliminating the = sign? It's part of the xml structure.

If you'd like to remove it you'd have to convert attributes into elements, making the current element a node.

On "what end" of the xml structure are you? : Do you have to create an xml file with elements but no attributes or are you trying to work with a given xml structure?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mhilligas Ten Centuries Points: 1354 More actions · Answer 2

Thanks for your quick response.

I personally like the ='s type of syntax.

I am converting the xml data to a varchar and sending the string to ORACLE as a parameter in a SP and the ORACLE people I am working with cannot parse this type of syntax. I think they are just not doing it right.

I am new to this powerful SQL data type.

So basically ORACLE recieves a XML formatted string and need to convert it to their XMLTYPE variable and parse it.

I hope this clarifies my position.

Thanks again

Regards

Mike

LutzM SSC Guru Points: 107049 More actions · Answer 3

Would you please be a little more specific on how you convert the xml data?

What I'd like to know is

a) Where do you get the xml data from (select statement on a table, import from a file or something else),

b) What does the xml file you need to modify look like (short sample), and

c) How is the file transferred to Oracle? (SSIS task on SQL Server side, flat file export/import, ...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

Check out "The Art of XSD" by Jacob Sebastian in the Books section of SSC !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mhilligas Ten Centuries Points: 1354 More actions · Answer 5

Here is the information you requested.

Loading XML Variable:

DECLARE @x xml

set @x = (SELECT * FROM dbo.vwGetBatchingResultsForOracle

WHERE (BatchNumber = @BatchNumber)

FOR XML RAW('item'), ROOT('items'), TYPE)

Convert to varchar to send to ORACLE:

DECLARE @XMLchar nvarchar(max)

set @XMLchar = convert(nvarchar(max),@x)

At this point I call a ORACLE SP thru a Linked Server using OPENQUERY

This all works fine and ORACLE does receive the XML formatted varchar

My issue is that I need to use ELEMENTS instead of ATTRIBUTES of ELEMENTS.

This would eliminate the = sign syntax.

Is there a way to code the select statement to generate ELEMENTS only?

Thanks again for your help.

Regards

Mike

LutzM SSC Guru Points: 107049 More actions · Answer 6

It looks like there's an issue with the way you format the xml data.

Maybe the following combination can be used to get the right format:

FOR XML RAW('item'),ROOT('items'), ELEMENTS, TYPE.

For details on how to use FOR XML clauses I'd recommend you look into Jacob Sebastians series "XML Workshop" (maybe start with XML Workshop I [/url].

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mhilligas Ten Centuries Points: 1354 More actions · Answer 7

I tried your syntax change.

It WORKS GREAT!!!!!!

Thank You Very Much.

I hope all is well with you.

Have a good week and weekend 🙂

Regards

Michael J. Hilligas

mhilligas Ten Centuries Points: 1354 More actions · Answer 8

Imu92....

Thanks again for your help.

I have one more question.

Is there any way to pass a Table as a parameter to a SP in SQL 2005

One other issue is that the SP is a ORACLE SP being called using OPENQUERY in a LINKED Server.

I have had no success. I understand that SQL 2008 has this support, but I am not sure whether the OLEDB provider MSDAORA will ever support it.

Any insite would be valuble.

Regards

Michael J. Hilligas

LutzM SSC Guru Points: 107049 More actions · Answer 9

Regarding your first question:

AFAIK, it's not possible to pass a table to a SP in SS2K5. It's a different story in SS2K8...

Other options to deal with such a problem is to either pass data as xml structure or as delimited list.

The biggest disadvantage:

The larger the data volume will be, the more performance of such procs will suffer.

Maybe you could pass table name together with select criteria as separate parameter to the proc and build the query inside the proc.

All depends on the requirement...

Your second question:

Simple answer: I don't know. You could either search this site for the issue or start another thread.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mhilligas Ten Centuries Points: 1354 More actions · Answer 10

Lutz,

Thanks for your reply.

I have tried to send the xml variable in the SP call but the MSDAORA OleDB Provider does not like it.

I need to spend some time, IF I HAD ANY, looking at SQL2008.

Regards

Mike

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 11

- any chance on using SSIS to migrate your data from SQL to Oracle or the other way around ?

- if not, maybe a SQLCLR proc can help out. Also keep in mind that this will have its consequences towards your sqlinstance (it wouldn't be the first time SQLCLR tears down your whole sql instance ..)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me