I'm trying to read the XML document in SQL Server 2005 using following steps
1. Set the XMl string to the variable
e.g.
SET @XMLContents =
<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Infopath:-myXSD-2007-04-19T09-42-59" solutionVersion="1.0.0.2" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///D:\Op-Risk\Infopath\manifest.xsf" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-04-19T09:42:59" xml:lang="en-us">
    <my:EmpID>1349</my:EmpID>
    <my:EmpName>shashi</my:EmpName>
    <my:EmpDept>rd</my:EmpDept>
</my:myFields>

2. Prepare the XML document using sp_xml_preparedocument Sp.
e.g. preparedocument @idoc OUTPUT, @XMLContents

3. SELECT the columns using
SELECT *
FROM OPENXML(@idoc, '/' , 2)
WITH( EmpID VARCHAR(50) '.' ,
        EmpName VARCHAR(50) '.',
        EmpDept VARCHAR(50) '.'
   )

Which results into following

EmpID EmpName EmpDept
---------------------------------------------------------
1349 shashi rd 1349 shashi rd 1349 shashi rd

where i'm getting all the column values separated by a space for each column that is say for empId column the result is combination of EmpId + EmpName + EmpDept

How can get the separated values for each column.

Thanks in Advance.









Shashikant Deore

try this,

declare @idoc int

exec sp_xml_preparedocument @idoc OUTPUT,'<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Infopath:-myXSD-2007-04-19T09-42-59" solutionVersion="1.0.0.2" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///D:\Op-Risk\Infopath\manifest.xsf" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-04-19T09:42:59" xml:lang="en-us">
    <my:EmpID>1349</my:EmpID>
    <my:EmpName>shashi</my:EmpName>
    <my:EmpDept>rd</my:EmpDept>
</my:myFields>','<root xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-04-19T09:42:59"/>'

SELECT *
FROM OPENXML(@idoc, '/my:myFields' , 2)
WITH( EmpID VARCHAR(50) 'my:EmpID' ,
        EmpName VARCHAR(50) 'my:EmpName',
        EmpDept VARCHAR(50) 'my:EmpDept'
   )