Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

vb.net XML SQL

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
dastaub
Asked:
dastaub
1 Solution
 
Ioannis ParaskevopoulosCommented:
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
 
dastaubAuthor Commented:
Thank You
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now