Passing Large data into XML

Question

Hi,

I wanted to pass a large data into a variable the data looks like this:

[People Manager].[People Manager].&[2936],[People Manager].[People Manager].&[12692],[People Manager].[People Manager].&[8550],[People Manager].[People Manager].&[9808],[People Manager].[People Manager].&[6723],[People Manager].[People Manager].&[399],[People Manager].[People Manager].&[2280],[People Manager].[People Manager].&[12652],[People Manager].[People Manager].&[6539],[People Manager].[People Manager].&[6538],[People Manager].[People Manager].&[9708],[People Manager].[People Manager].&[12772],[People Manager].[People Manager].&[13917],[People Manager].[People Manager].&[12367],[People Manager].[People Manager].&[2928],[People Manager].[People Manager].&[1612],[People Manager].[People Manager].&[7357],[People Manager].[People Manager].&[7358],[People Manager].[People Manager].&[2479],[People Manager].[People Manager].&[273],[People Manager].[People Manager].&[12718],[People Manager].[People Manager].&[13132],[People Manager].[People Manager].&[8972],[People Manager].[People Manager].&[8971],[People Manager].[People Manager].&[8598],[People Manager].[People Manager].&[6853],[People Manager].[People Manager].&[659],[People Manager].[People Manager].&[12758],[People Manager].[People Manager].&[13340],[People Manager].[People Manager].&[7140],[People Manager].[People Manager].&[11837],[People Manager].[People Manager].&[10098],[People Manager].[People Manager].&[2408],[People Manager].[People Manager].&[297],[People Manager].[People Manager].&[12699],[People Manager].[People Manager].&[6757],[People Manager].[People Manager].&[2331],[People Manager].[People Manager].&[170],[People Manager].[People Manager].&[12675],[People Manager].[People Manager].&[8512],[People Manager].[People Manager].&[8511],[People Manager].[People Manager].&[6629],[People Manager].[People Manager].&[6628],[People Manager].[People Manager].&[7885],[People Manager].[People Manager].&[12729],[People Manager].[People Manager].&[9904],[People Manager].[People Manager].&[255],[People Manager].[People Manager].&[2460],[People Manager].[People Manager].&[12714],[People Manager].[People Manager].&[13117],[People Manager].[People Manager].&[11727],[People Manager].[People Manager].&[8585],[People Manager].[People Manager].&[9862],[People Manager].[People Manager].&[6825],[People Manager].[People Manager].&[8586],[People Manager].[People Manager].&[8506],[People Manager].[People Manager].&[13031],[People Manager].[People Manager].&[14506],[People Manager].[People Manager].&[8048],[People Manager].[People Manager].&[6614],[People Manager].[People Manager].&[9754],[People Manager].[People Manager].&[157],[People Manager].[People Manager].&[195],[People Manager].[People Manager].&[3027],[People Manager].[People Manager].&[6788],[People Manager].[People Manager].&[2435],[People Manager].[People Manager].&[11722],[People Manager].[People Manager].&[2347],[People Manager].[People Manager].&[177],[People Manager].[People Manager].&[12681],[People Manager].[People Manager].&[8519],[People Manager].[People Manager].&[6655],[People Manager].[People Manager].&[451],[People Manager].[People Manager].&[12737],[People Manager].[People Manager].&[6949],[People Manager].[People Manager].&[686],[People Manager].[People Manager].&[2110],[People Manager].[People Manager].&[7966],[People Manager].[People Manager].&[8919],[People Manager].[People Manager].&[6267],[People Manager].[People Manager].&[7481],[People Manager].[People Manager].&[7965],[People Manager].[People Manager].&[2436],[People Manager].[People Manager].&[266]

And the above data is more than 8000 characters, for obvious reasons no data type supports data having more than 8000 characters. Hence was thinking whether i can do the same using XML data types for storing the above data and afterwards using the same in the same Stored proc as a parameter for another Stored proc being called just like a Varchar data type.    

---

Rojit

Answer 1

What about [N]VARCHAR(MAX)? It can store 2^31-1 bytes.

Answer 2

Nope, Its giving error of 8000 characters limitation. my length can go to anything between 10000-20000 characters.

---

Rojit

Answer 3

Where do you get your data from? Please post a concise (and complete, but not containing 8K+ data) example.

Answer 4

Stefan,

We are getting the data from SSAS cube resultset using link server and using it further in the Stored proc for further processing based upon the data set returned from cube. So in this case we have to store the data being returned in some variable for further usAGE.

---

Rojit

Answer 5

You can't pass XML around over linked server either.

Try the SSAS forum for a more sensible way to do this.

Answer 6

I dont need to pass XML over linked server i just need to need to store the dataset returned from linked server to a xml data type.

---

Rojit

Answer 7

I still don't get the point. Can you elaborate your question? Why can't you call a stored procedure like

CREATE PROCEDURE p_XML @Xml XML 
AS
-- Do something with @Xml.

or

CREATE PROCEDURE p_CLOB @CLOB NVARCHAR(MAX)
AS
-- Do something with @CLOB.

or

CREATE PROCEDURE p_BLOB @BLOB VARBINARY(MAX)
AS
-- Do something with @BLOB.