Professional Web Applications Themes

Writing "text" columns in ADO.NET - Microsoft SQL / MS SQL Server

G'day all - can anyone help with code/link to info on writing a large XML string to a text column in SQL Server 2000? I currently have hundreds of XML files on disk, and use ASP to display one at a time to intranet client (IE5+), would like to be able to display more that one per request so thought that putting them in the db would reduce file I/O. XML does not need to be pd (e.g. it could be a BLOB), just transformed using existing XSLT. Any/all help appreciated! Thomas Williams...

  1. #1

    Default Writing "text" columns in ADO.NET

    G'day all - can anyone help with code/link to info on
    writing a large XML string to a text column in SQL Server
    2000? I currently have hundreds of XML files on disk, and
    use ASP to display one at a time to intranet client
    (IE5+), would like to be able to display more that one
    per request so thought that putting them in the db would
    reduce file I/O. XML does not need to be pd (e.g. it
    could be a BLOB), just transformed using existing XSLT.

    Any/all help appreciated!

    Thomas Williams
    Thomas Williams Guest

  2. #2

    Default Re: Writing "text" columns in ADO.NET

    Thanks Bob - the xml files could get as large as 4MB (so I went for text
    over varchar)! The next big question is how can I get my original stored
    proc which returns XML using FOR EXPLICIT to just insert into the table
    without calling it from a client app e.g. the ultimate would be:

    INSERT INTO t (id, largeXmlField) VALUES ('1', EXEC
    (originalSpToReturnXml param1, param2))

    Is this possible, or am I missing something? I can provide more of a
    sample if necessary. On reflection I think that calling the stored proc
    from VB.NET then an INSERT would not be too much trouble...

    Thanks again Bob!

    Thomas Williams


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Thomas Williams Guest

  3. #3

    Default Writing "text" columns in ADO.NET

    Thanks Bob - the xml files could get as large as 4MB (so
    I went for text over varchar)! The next big question is
    how can I get my original stored proc which returns XML
    using FOR EXPLICIT to just insert into the table
    without calling it from a client app e.g. the ultimate
    would be:

    INSERT INTO t (id, largeXmlField) VALUES ('1', EXEC
    (originalSpToReturnXml param1, param2))

    Is this possible, or am I missing something? I can
    provide more of a sample if necessary. On reflection I
    think that calling the stored proc from VB.NET then an
    INSERT would not be too much trouble...

    Thanks again Bob!

    Thomas Williams
    >-----Original Message-----
    >All you need to do is create a field in your table with
    >either text or ntext datatype... and write the data as a
    >string to that field...
    >
    >If you know your XML files will always be less than 8K
    in
    >size then you could use a varchar datatype instead...
    >
    >A BLOB would be too much of a pain for such a simple
    >scenario...
    >
    >So, are you asking for ADO code samples to put the data
    in
    >the table and get it back...? Probably a question for
    the
    >ADO newsgroup although I can provide sample if you need
    >them.
    >
    >Cheers
    >
    >
    >>-----Original Message-----
    >>G'day all - can anyone help with code/link to info on
    >>writing a large XML string to a text column in SQL
    Server
    >>2000? I currently have hundreds of XML files on disk,
    and
    >>use ASP to display one at a time to intranet client
    >>(IE5+), would like to be able to display more that one
    >>per request so thought that putting them in the db
    would
    >>reduce file I/O. XML does not need to be pd (e.g.
    it
    >>could be a BLOB), just transformed using existing XSLT.
    >>
    >>Any/all help appreciated!
    >>
    >>Thomas Williams
    >>.
    >>
    >.
    >
    Thomas Williams Guest

  4. #4

    Default Writing "text" columns in ADO.NET

    Ok, if I am reading this correctly you want to use you XML
    returning query and pump the results straight back into
    another table...?

    This shouldn't be too bad.... are you using MSXML to
    handle the XML returned by the query? If so you can just
    do an insert using the XML property of the MSXML
    DOMDoent object.... which is essentially what your last
    comment suggested....

    This might be a better way: I am not sure how feasible
    this is but could you create a stored proc that call the
    XML returning proc into an ntext (or text) variable, and
    then do the insert directly... saves all the ADO, XML and
    network marshalling business...

    So spA would declare a temp var as ntext/text, call the
    XML returning sp (spB), place the results into the temp
    variable, and then use that in an insert....yeah, I think
    that makes sense and can't see any problem with it
    (although I am assuming the XML will appear to SQL as a
    string when returned by the proc)... if you can avoid
    hitting SQL twice then do it (you'd be hitting it twice if
    you first used ADO to get the XML and then used it again
    to do the insert). This will increase the scalability of
    your app, and reduce it's resource hungriness...

    Not sure if that helps... if I am on the wrong track then
    you'd better provide some more detail...

    Cheers
    >-----Original Message-----
    >Thanks Bob - the xml files could get as large as 4MB (so
    >I went for text over varchar)! The next big question is
    >how can I get my original stored proc which returns XML
    >using FOR EXPLICIT to just insert into the table
    >without calling it from a client app e.g. the ultimate
    >would be:
    >
    >INSERT INTO t (id, largeXmlField) VALUES ('1', EXEC
    >(originalSpToReturnXml param1, param2))
    >
    >Is this possible, or am I missing something? I can
    >provide more of a sample if necessary. On reflection I
    >think that calling the stored proc from VB.NET then an
    >INSERT would not be too much trouble...
    >
    >Thanks again Bob!
    >
    >Thomas Williams
    >
    >>-----Original Message-----
    >>All you need to do is create a field in your table with
    >>either text or ntext datatype... and write the data as a
    >>string to that field...
    >>
    >>If you know your XML files will always be less than 8K
    >in
    >>size then you could use a varchar datatype instead...
    >>
    >>A BLOB would be too much of a pain for such a simple
    >>scenario...
    >>
    >>So, are you asking for ADO code samples to put the data
    >in
    >>the table and get it back...? Probably a question for
    >the
    >>ADO newsgroup although I can provide sample if you need
    >>them.
    >>
    >>Cheers
    >>
    >>
    >>>-----Original Message-----
    >>>G'day all - can anyone help with code/link to info on
    >>>writing a large XML string to a text column in SQL
    >Server
    >>>2000? I currently have hundreds of XML files on disk,
    >and
    >>>use ASP to display one at a time to intranet client
    >>>(IE5+), would like to be able to display more that one
    >>>per request so thought that putting them in the db
    >would
    >>>reduce file I/O. XML does not need to be pd (e.g.
    >it
    >>>could be a BLOB), just transformed using existing XSLT.
    >>>
    >>>Any/all help appreciated!
    >>>
    >>>Thomas Williams
    >>>.
    >>>
    >>.
    >>
    >.
    >
    Bob Guest

  5. #5

    Default Re: Writing "text" columns in ADO.NET

    Hey Bob (once again, thanks for the reply) -

    I guess I should explain more of the problem and why I thought of the
    solution: currently, the XML data I'm using (from files) is 1 XML file =
    1 HTML table = 1 'report', which works well and fairly quickly as no db
    hits are required. I had already created the XML files at the start of
    each month (more on that later). Now my clients are requesting multiple
    XML 'reports' per ASP request. These XML 'reports' are static,
    historical data, and rather than serve them up dynamically I figured
    storing the results as an XML string in a table, hitting the db once for
    all the required XML and then transforming the XML via existing XSLT -
    this would also reduce my reliance on the file system and give better
    portability at the expense of a larger db. I would do this XML table
    insert stuff once a month (currently I churn out XML files for 2 hours
    once a month). Phew!

    Now, you've hit on a great solution mate, but infortunately 'text'
    datatypes are invalid for local variables. My XML could be more than
    8000 characters long, so that precludes the use of varchar. Is it
    possible to do a call like (below), and what would the syntax be?
    >INSERT INTO t (id, largeXmlField) VALUES ('1', EXEC
    >(originalSpToReturnXml param1, param2))
    Thanks for your thoughts on the matter Bob!

    Thomas Williams

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Thomas Williams Guest

  6. #6

    Default Re: Writing "text" columns in ADO.NET

    Gidday,

    I don't have time to check this out fully right now but something to think
    about....

    Try using something along these lines:

    INSERT INTO tblXMLTest2 (XMLString)
    SELECT id, XMLString
    FROM tblXMLTest
    WHERE id=ID

    which using your table and column names would be something like:

    INSERT INTO t (id, largeXmlField)
    SELECT id,XMLString
    FROM tblXMLTest
    WHERE id=ID

    Where tblXMLTest is the source table, tblXMLTest2 is the destination table,
    id=ID is whatever criteria you need to select the specific record(s)
    required, and XMLString is the column containing the source XML

    As we are not using the = assignment operator we can deal with ntext without
    error.... well, that's my theory!

    I also managed to split this into 2 SP's (below) so that the first calls the
    second to get the XML, then performs the INSERT....

    **********************************
    CREATE PROCEDURE spXMLTest
    (
    ID int
    )
    AS

    --Note the ID parameter must be passed to second SP to retrieve the correct
    record
    --Next line gets XML using spXMLTest2 and inserts into the table
    INSERT INTO tblXMLTest2 (XMLString) EXEC spXMLTest2 ID

    GO

    **********************************

    CREATE PROCEDURE spXMLTest2
    (
    ID int
    )
    AS

    --This just returns the XML to the first SP
    SELECT XMLString FROM tblXMLTest WHERE id=ID
    GO


    This is (roughly) what you were after isn't it? Let me know how you go...
    and if this is going into prod make sure you add error handling in case
    spXMLTest2 (in my example) returns nothing....

    Cheers

    "Thomas Williams" <anonymousdevdex.com> wrote in message
    news:%233FFFaORDHA.1016TK2MSFTNGP11.phx.gbl...
    > Hey Bob (once again, thanks for the reply) -
    >
    > I guess I should explain more of the problem and why I thought of the
    > solution: currently, the XML data I'm using (from files) is 1 XML file =
    > 1 HTML table = 1 'report', which works well and fairly quickly as no db
    > hits are required. I had already created the XML files at the start of
    > each month (more on that later). Now my clients are requesting multiple
    > XML 'reports' per ASP request. These XML 'reports' are static,
    > historical data, and rather than serve them up dynamically I figured
    > storing the results as an XML string in a table, hitting the db once for
    > all the required XML and then transforming the XML via existing XSLT -
    > this would also reduce my reliance on the file system and give better
    > portability at the expense of a larger db. I would do this XML table
    > insert stuff once a month (currently I churn out XML files for 2 hours
    > once a month). Phew!
    >
    > Now, you've hit on a great solution mate, but infortunately 'text'
    > datatypes are invalid for local variables. My XML could be more than
    > 8000 characters long, so that precludes the use of varchar. Is it
    > possible to do a call like (below), and what would the syntax be?
    >
    > >INSERT INTO t (id, largeXmlField) VALUES ('1', EXEC
    > >(originalSpToReturnXml param1, param2))
    >
    > Thanks for your thoughts on the matter Bob!
    >
    > Thomas Williams
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Bob Guest

  7. #7

    Default Re: Writing "text" columns in ADO.NET

    I just reread your post and I think I told you to do what you already
    suggested yourself... sorry... however, I did test the samples I provided
    and they worked fine... your's should too with minor tweaking...

    Cheers

    "Bob" <bobnospam.com> wrote in message
    news:ukX%23zjSRDHA.1072TK2MSFTNGP10.phx.gbl...
    > Gidday,
    >
    > I don't have time to check this out fully right now but something to think
    > about....
    >
    > Try using something along these lines:
    >
    > INSERT INTO tblXMLTest2 (XMLString)
    > SELECT id, XMLString
    > FROM tblXMLTest
    > WHERE id=ID
    >
    > which using your table and column names would be something like:
    >
    > INSERT INTO t (id, largeXmlField)
    > SELECT id,XMLString
    > FROM tblXMLTest
    > WHERE id=ID
    >
    > Where tblXMLTest is the source table, tblXMLTest2 is the destination
    table,
    > id=ID is whatever criteria you need to select the specific record(s)
    > required, and XMLString is the column containing the source XML
    >
    > As we are not using the = assignment operator we can deal with ntext
    without
    > error.... well, that's my theory!
    >
    > I also managed to split this into 2 SP's (below) so that the first calls
    the
    > second to get the XML, then performs the INSERT....
    >
    > **********************************
    > CREATE PROCEDURE spXMLTest
    > (
    > ID int
    > )
    > AS
    >
    > --Note the ID parameter must be passed to second SP to retrieve the
    correct
    > record
    > --Next line gets XML using spXMLTest2 and inserts into the table
    > INSERT INTO tblXMLTest2 (XMLString) EXEC spXMLTest2 ID
    >
    > GO
    >
    > **********************************
    >
    > CREATE PROCEDURE spXMLTest2
    > (
    > ID int
    > )
    > AS
    >
    > --This just returns the XML to the first SP
    > SELECT XMLString FROM tblXMLTest WHERE id=ID
    > GO
    >
    >
    > This is (roughly) what you were after isn't it? Let me know how you go...
    > and if this is going into prod make sure you add error handling in case
    > spXMLTest2 (in my example) returns nothing....
    >
    > Cheers
    >
    > "Thomas Williams" <anonymousdevdex.com> wrote in message
    > news:%233FFFaORDHA.1016TK2MSFTNGP11.phx.gbl...
    > > Hey Bob (once again, thanks for the reply) -
    > >
    > > I guess I should explain more of the problem and why I thought of the
    > > solution: currently, the XML data I'm using (from files) is 1 XML file =
    > > 1 HTML table = 1 'report', which works well and fairly quickly as no db
    > > hits are required. I had already created the XML files at the start of
    > > each month (more on that later). Now my clients are requesting multiple
    > > XML 'reports' per ASP request. These XML 'reports' are static,
    > > historical data, and rather than serve them up dynamically I figured
    > > storing the results as an XML string in a table, hitting the db once for
    > > all the required XML and then transforming the XML via existing XSLT -
    > > this would also reduce my reliance on the file system and give better
    > > portability at the expense of a larger db. I would do this XML table
    > > insert stuff once a month (currently I churn out XML files for 2 hours
    > > once a month). Phew!
    > >
    > > Now, you've hit on a great solution mate, but infortunately 'text'
    > > datatypes are invalid for local variables. My XML could be more than
    > > 8000 characters long, so that precludes the use of varchar. Is it
    > > possible to do a call like (below), and what would the syntax be?
    > >
    > > >INSERT INTO t (id, largeXmlField) VALUES ('1', EXEC
    > > >(originalSpToReturnXml param1, param2))
    > >
    > > Thanks for your thoughts on the matter Bob!
    > >
    > > Thomas Williams
    > >
    > > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > > Don't just participate in USENET...get rewarded for it!
    >
    >

    Bob Guest

Similar Threads

  1. "Text image" back to "Editable text"
    By Prabudass@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 5
    Last Post: August 4th, 03:40 PM
  2. Text as paths, when below 32 point, "l" and"-" become strokes not boxes
    By Brad_Wallwork@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: January 23rd, 02:19 AM
  3. Replies: 0
    Last Post: December 2nd, 11:18 AM
  4. Text "writing itself" effect. need help
    By John in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: January 30th, 12:17 AM
  5. How to make the "search text" feature work with non "txt" file
    By Sharon F in forum Windows XP/2000/ME
    Replies: 0
    Last Post: June 30th, 07:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139