Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vb.net XML SQL

Posted on 2014-03-13
3
Medium Priority
?
394 Views
Last Modified: 2014-03-14
The below function creates an XML document that is then written to disk.  Also below is an T-SQL command that converts part of the XML document into an SQL table.  It all works.  

QUESTION:  Can the step of writing the XML document to disk be skipped and the XML document object be sent directly to the T-SQL statement that is sent to SQL 2008?
    Public Function xCMP() As XDocument

        Return _
         <?xml version="1.0"?>
         <CMPDocument>
             <Title>EPOWERdoc - CMP Document</Title>
             <Client>
                 <DateProcessed><%= Dt2Db(Now()) %></DateProcessed>
                 <CliNo><%= Clino %></CliNo>
                 <CliServerName><%= CliServerName %></CliServerName>
                 <RAMTotal><%= RAMTotal %></RAMTotal>
                 <RAMSQL><%= RAMSQL %></RAMSQL>
                 <LastFDBUpdate><%= LastFDBUpdate %></LastFDBUpdate>
             </Client>
             <CMPData>
                 <%= xDiskDrives %>
                 <%= xVolume %>
                 <%= xExecutables %>
                 <%= xEPDApplicationErrors %>
                 <%= xEventLog %>
             </CMPData>
         </CMPDocument>
    End Function

Open in new window


---------------------------  T-SQL ----------------------------
DECLARE @x xml
SELECT @x = SQLs
FROM OPENROWSET (BULK 'C:\EPD\EPD315\Bin\CMP\1_201403130224.xml', SINGLE_BLOB) AS Solution(SQLs)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/DiskDrives', 2)
WITH (
      [Drive] varchar(500)
      ,TotalSpace varchar(500)
      ,FreeSpace varchar(500)
      )

EXEC sp_xml_removedocument @hdoc

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

Open in new window

0
Comment
Question by:dastaub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 2000 total points
ID: 39928464
Hi,

Since SQL has an xml data type, you may add it to the procedure's parameters and then directly pass an xml to the sp instead of writting it to the disk.

For calling it through your program you may check the following link:
http://msdn.microsoft.com/en-us/library/34e4kcbw(v=vs.110).aspx

Giannis
0
 

Author Comment

by:dastaub
ID: 39929567
Thank You
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question