I have a SOAP formatted message. I am passing this message as a string from a
C# client to a SQL Stored Procedure.

I would like to read this SOAP formatted message and store the data in the
SOAP message in appropriate columns in a SQL table. How can I achieve this?

Can OPENXML be used for this purpose?
--
gg

Is configuring IIS to be used in conjunction with SQLXML a requirement for
the question I am asking?

My eventual goal is to insert the data present in the SOAP message into a
SQL table.

Please let me know how I can achieve this goal.

Thanks

Could you just store in a nvarchar(max) ?

--
William Stacey [MVP]

"gudia" <gudia (AT) discussions (DOT) microsoft.com> wrote

There are a couple of approaches. You could pass the entire SOAP XML doc to
a stored procedure and use OPENXML to shred it into tables, or you could
create an annotated XSD schema that maps the elements/attributes in your
SOAP message to the tables/column in the database and use the SQLXML Bulk
Load component.

Neither of these approaches requires a SQLXML IIS site.

Cheers,
Graeme

--
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com


"gudia" <gudia (AT) discussions (DOT) microsoft.com> wrote

Is configuring IIS to be used in conjunction with SQLXML a requirement for
the question I am asking?

My eventual goal is to insert the data present in the SOAP message into a
SQL table.

Please let me know how I can achieve this goal.

Thanks

I have displaying my SOAP message below. Could you show me what T-SQL
statement using OPENXML or otherwise I can use to import the data in this
SOAP message into various columns in a SQL table.

My SQL table has the following columns:

LogEntry varchar(255)
Message varchar(255)
Title varchar(255)
Category varchar(50)
Priority int
EventID int
Severity varchar(255)
MachineName varchar(50)
TimeStampVal datetime
ErrorMessages varchar(255)
ExtendedProperties varchar(255)
AppDomainName varchar(50)
ProcessID int
ProcessName varchar(255)
ThreadName varchar(50)

<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.EnterpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%2 0Version%3D1.0.0.0%2C%20Culture%3Dneutral%2C%20Pub licKeyToken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
--
gg


"Graeme Malcolm" wrote:

Here's one way (I wasn't sure what you want in the LogEntry column, since
that's an XML element that contains all the others - so I used the id
attribute). This example uses a temporary table with the columns you
specified and I've hardcoded the SOAP message as a variable - in reality
you'd pass it to a stored procedure as a parameter. I suggest you take some
time to examine the documentation on OPENXML in Books Online to tweak this
to do exactly what you want it to.

USE Tempdb

CREATE TABLE #TestTable
(
LogEntry varchar(255),
Message varchar(255),
Title varchar(255),
Category varchar(50),
Priority int,
EventID int,
Severity varchar(255),
MachineName varchar(50),
TimeStampVal datetime,
ErrorMessages varchar(255),
ExtendedProperties varchar(255),
AppDomainName varchar(50),
ProcessID int,
ProcessName varchar(255),
ThreadName varchar(50)
)

DECLARE @doc nvarchar(2000)
SET @doc = '<SOAP-ENV:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.EnterpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%2 0Version%3D1.0.0.0%2C%20Culture%3Dneutral%2C%20Pub licKeyToken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @doc, '<ns
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.EnterpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%2 0Version%3D1.0.0.0%2C%20Culture%3Dneutral%2C%20Pub licKeyToken%3Dnull"/>'

INSERT #TestTable
SELECT * FROM OPENXML(@hDoc, 'SOAP-ENV:Envelope/SOAP-ENV:Body/a1:LogEntry',
2)
WITH
(
LogEntry varchar(255) '@id',
message varchar(255),
title varchar(255),
category varchar(50),
priority int,
eventId int,
severity varchar(255),
machineName varchar(50),
timeStampVal datetime,
errorMessages varchar(255),
extendedProperties varchar(255),
appDomainName varchar(50),
processId int,
processName varchar(255),
threadName varchar(50)
)

SELECT * FROM #TestTable

DROP TABLE #TestTable

--
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com


"gudia" <gudia (AT) discussions (DOT) microsoft.com> wrote

I have displaying my SOAP message below. Could you show me what T-SQL
statement using OPENXML or otherwise I can use to import the data in this
SOAP message into various columns in a SQL table.

My SQL table has the following columns:

LogEntry varchar(255)
Message varchar(255)
Title varchar(255)
Category varchar(50)
Priority int
EventID int
Severity varchar(255)
MachineName varchar(50)
TimeStampVal datetime
ErrorMessages varchar(255)
ExtendedProperties varchar(255)
AppDomainName varchar(50)
ProcessID int
ProcessName varchar(255)
ThreadName varchar(50)

<SOAP-ENV:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<a1:LogEntry id="ref-1"
xmlns:a1="http://schemas.microsoft.com/clr/nsassem/Microsoft.Practices.EnterpriseLibrary.Logging/Microsoft.Practices.EnterpriseLibrary.Logging%2C%2 0Version%3D1.0.0.0%2C%20Culture%3Dneutral%2C%20Pub licKeyToken%3Dnull">
<message id="ref-3">Msg successfully populated</message>
<title id="ref-4">Title1</title>
<category id="ref-5">Cat1</category>
<priority>100</priority>
<eventId>16</eventId>
<severity>Information</severity>
<machineName id="ref-6">MACH1</machineName>
<timeStamp>2005-05-20T15:24:18.4850496-05:00</timeStamp>
<errorMessages xsi:null="1"/>
<extendedProperties xsi:null="1"/>
<appDomainName id="ref-7">B1.exe</appDomainName>
<processId id="ref-8">3932</processId>
<processName id="ref-9">C:\B1\bin\Debug\B1.exe</processName>
<threadName xsi:null="1"/>
<win32ThreadId id="ref-10">244</win32ThreadId>
</a1:LogEntry>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
--
gg


"Graeme Malcolm" wrote:

Thanks so much Graeme. That worked.

Appreciate your help.

"Graeme Malcolm" wrote: