Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2007, 03:38 AM
Registered User
 
Join Date: Oct 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to shashideore
Default XMl Document Reading in SQL Server

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
 
Old April 26th, 2007, 07:16 AM
Registered User
 
Join Date: Apr 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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'
   )






Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server reading from XML gunjan.sh SQL Server 2005 1 June 6th, 2008 03:35 PM
Reading xml string from remote server niravp Classic ASP XML 2 June 10th, 2006 02:37 PM
Write Nested XML document into SQL Server tables boondocksaint20 VB.NET 0 May 2nd, 2006 12:35 PM
how to send an XML document to a server? limrick XML 0 April 29th, 2005 06:39 AM
Help sending XML file to server, reading response bradartigue XML 1 September 5th, 2003 07:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.