Deserialize data in xml column to a table

Question

Deserialize data in xml column to a table

G33kKahuna

SSCommitted

Points: 1726
More actions
April 17, 2008 at 9:00 pm

#93353

I absolutely love XML data type but I think I have reached my limits on this new requirement I am working on. The application is a 3 tier model, application will be configured to write a serailized version of the business object data in a xml column. Xml looks like,
[bo]
[row number=1]
value[/column1]
[column2]value[/column2]
...
...
[columnN]value[/columnN]
[/row]
[/bo]
An external process, agnostic of xml, needs to call a stored proc that returns a table, which is the deserialized version of the xml data. The stored proc return table needs to look like,
column1 | column2 | .... | columnN
value | value | .... | value
Here is the curve ball, the format will be the same as above but the bo and column names could vary depending on the business object writing to this column.
any guidance/ suggestions is much appreciated.
thanks in advance.

Viewing 14 posts - 1 through 13 (of 13 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

I think you'll have to use dynamic sql to return a dynamic set of columns. I'd write a function which takes the xml and returns a sql query string, and I'd then call and execute that from the sp.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

G33kKahuna SSCommitted Points: 1726 More actions · Answer 2

thanks for the response.

well, this kind of goes back to my quesiton; how would one write a generic XML column extracter?

thanks

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Does this help?

declare @x xml

set @x = '

<bo>

<row number="1">

<column1>value</column1>

<column2>value</column2>

<column3>value</column3>

</row>

<row number="2">

<column1>value</column1>

<column4>value</column4>

<column5>value</column5>

</row>

</bo>'

select @x.query('distinct-values(for $a in //row/* return local-name($a))')

/* Results

column1 column2 column3 column4 column5

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

G33kKahuna SSCommitted Points: 1726 More actions · Answer 4

thanks. This definitely gives me a starting point.

also, I feel left out with FLOWR, are there any good online tutorials?

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Kaushik Kumar (4/18/2008)
thanks. This definitely gives me a starting point.
also, I feel left out with FLOWR, are there any good online tutorials?

I don't know - sorry...

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Kaushik Kumar (4/18/2008)
thanks. This definitely gives me a starting point.
also, I feel left out with FLOWR, are there any good online tutorials?

You might want to start with this article: http://www.sqlservercentral.com/articles/SS2K5+-+XML/2840/

😎

G33kKahuna SSCommitted Points: 1726 More actions · Answer 7

thanks Lynn.

is there anyway to make the above query return as a comma seperated list?

thanks

G33kKahuna SSCommitted Points: 1726 More actions · Answer 8

Nevermind, figured out the comma part.

ryan,

the bo xml has about 450 columns, column names vary from 10 chars to 25 chars long. I fear I will not be able to fit everything in the dynamic sql. Any thoughts on how to get past this?

As an alternative, I was thinking of storing a typed dataset in the database with its xsl. Employ a SQL CLr, to recontruct a DataSet at runtime and return it. thoughts?

thanks

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 9

Kaushik Kumar (4/20/2008)
Nevermind, figured out the comma part.

Two way street here, Kaushik... how about telling us how you did that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RyanRandall SSChampion Points: 13623 More actions · Answer 10

I fear I will not be able to fit everything in the dynamic sql.

What is your basis for this fear?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

jimbobmcgee Ten Centuries Points: 1108 More actions · Answer 11

Kaushik Kumar (4/20/2008)
I fear I will not be able to fit everything in the dynamic sql. Any thoughts on how to get past this?

I had a similar problem once with SQL2000, where my dynamic SQL was over 2000 characters long, so wouldn't fit in an NVARCHAR variable (and so couldn't be run by sp_executesql).

Since, in 2000, you couldn't declare an NTEXT variable and work with it, I had to run it in two stages:

1. The front-end requested the dynamic SQL from the database, which was returned as an NTEXT

2. The front-end sent the returned NTEXT back to the database, via sp_executesql, and received the results in a table

I used a general 'DynamicSQL' table, with a UNIQUEIDENTIFIER and an NTEXT column, then instead of doing "@q = @q + 'SELECT ' + @a..."-type calls, I built it using WRITETEXT.

It wasn't pretty but it worked.

In 2005, however, I reckon NVARCHAR(MAX) might negate this requirement...

J.

G33kKahuna SSCommitted Points: 1726 More actions · Answer 12

here you go,

select @x.query('distinct-values(for $a in //row/* return concat(local-name($a),","))')

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 13

Perfect... thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)