insert or update using xml data

I have the following stored procedure.It is executing fine but not fetching required data from xml file.
I am passing entire  xml file as input parameter for this stored procedure.

I think some thing wrong with the path that is in openxml function.
alter PROCEDURE [dbo].[PHYSICIANPROFILE_xml] 
--@table_Xml XML
@tab_Xml text
AS
BEGIN
--declare @x xml
 --set @x='C:\Documents and Settings\sm38\Desktop\a'
DECLARE @idoc int
DECLARE @PRACTITIONERID int
DECLARE @DEPARTMENTID   int
DECLARE @STAFFCAREGORYID int
DECLARE @FROMYEAR int
DECLARE @TOYEAR int
DECLARE @LICENSE varchar(20)
DECLARE @NPI varchar(20)
DECLARE @PRIVILEGECHANGE char(1)
DECLARE @REPORTID varchar(50)
DECLARE @UPDATEDON datetime
DECLARE @ACTIVE char(1)
 
EXECUTE dbo.sp_xml_preparedocument @idoc  OUTPUT, @tab_Xml
 
 
SELECT @PRACTITIONERID=PRACTITIONERID,
        @DEPARTMENTID=DEPARTMENTID,
        @STAFFCAREGORYID=STAFFCAREGORYID,
        @FROMYEAR=FROMYEAR,
        @TOYEAR=TOYEAR,
        @LICENSE=LICENSE,
        @NPI=NPI,
        @PRIVILEGECHANGE=PRIVILEGECHANGE,
        @REPORTID=REPORTID,
        @UPDATEDON=UPDATEDON,
        @ACTIVE=ACTIVE
 FROM OpenXml(@idoc,'/qaworks/practitionerdetails/physicanprofile/f/v',1)
 WITH     (PRACTITIONERID int    '/qaworks/practitionerdetails/physicanprofile/f/k', 
           DEPARTMENTID int      '/qaworks/practitionerdetails/physicanprofile/f/k',
           STAFFCAREGORYID int   '/qaworks/practitionerdetails/physicanprofile/f/k',
           FROMYEAR int             '/qaworks/practitionerdetails/physicanprofile/f/k',
           TOYEAR int               '/qaworks/practitionerdetails/physicanprofile/f/k',
           LICENSE varchar(20)   '/qaworks/practitionerdetails/physicanprofile/f/k',
           NPI varchar(20)       '/qaworks/practitionerdetails/physicanprofile/f/k',
           PRIVILEGECHANGE char(1) '/qaworks/practitionerdetails/physicanprofile/f/k',
           REPORTID varchar(50)  '/qaworks/practitionerdetails/physicanprofile/f/k',
           UPDATEDON datetime    '/qaworks/practitionerdetails/physicanprofile/f/k',
           ACTIVE char(1)        '/qaworks/practitionerdetails/physicanprofile/f/k'  )       
 
     print(@DEPARTMENTID)
print(@STAFFCAREGORYID)
print(@FROMYEAR)
 
 
IF @PRACTITIONERID IN (SELECT PRACTID FROM PHYSICIANPROFILE)
BEGIN
  UPDATE PHYSICIANPROFILE SET DEPARTMENTID=@DEPARTMENTID,
                              STAFFCATEGORYID=@STAFFCAREGORYID,
                              FROMYEAR=@FROMYEAR,
                              TOYEAR=@TOYEAR,
                              LICENSE=@LICENSE,
                              NPI=@NPI,
                              PRIVILEGECHANGEREQUESTED=@PRIVILEGECHANGE,
                              REPORTID=@REPORTID,
                              UPDATEDON=@UPDATEDON,
                              ACTIVE=@ACTIVE
  WHERE PRACTID=@PRACTITIONERID
   IF @@ERROR<>0
       RETURN -1;
    ELSE
       RETURN 1; 
  END
  ELSE
  BEGIN
print(@PRACTITIONERID)
   INSERT INTO PHYSICIANPROFILE(PRACTID, 
                             DEPARTMENTID ,
                             STAFFCATEGORYID, 
                             FROMYEAR, 
                             TOYEAR,   
                             LICENSE,  
                             NPI,
                             PRIVILEGECHANGEREQUESTED,
                             REPORTID)
   VALUES (@PRACTITIONERID,
           @DEPARTMENTID,
           @STAFFCAREGORYID,
           @FROMYEAR,
           @TOYEAR,
           @LICENSE,
           @NPI,
           @PRIVILEGECHANGE,
           @REPORTID)
 
   IF @@ERROR<>0
       RETURN -1;
    ELSE
       RETURN 1; 
  END
end
 
 
xml code is as follows
===============================
 
<qaworks>
<practitionerdetails id="wsiofy45ybyz0qel50vou045" created="5/26/2009 6:21:11 PM" createdby="??">
<physicanprofile><f ><k>FROM1</k><v><![CDATA[2009]]></v></f><f ><k>TO1</k><v><![CDATA[2010]]></v></f><f ><k>PRACTITIONERNAME</k><v><![CDATA[Villare, Robert MD]]></v></f><f ><k>PRACTITIONERID</k><v><![CDATA[1]]></v></f><f ><k>LICENSE</k><v><![CDATA[LIC001]]></v></f><f ><k>NPI</k><v><![CDATA[NIP001]]></v></f><f ><k>DEPARTMENTNAME</k><v><![CDATA[Department3]]></v></f><f ><k>DEPARTMENTID</k><v><![CDATA[3]]></v></f><f ><k>STAFFCAREGORYNAME</k><v><![CDATA[Category 4]]></v></f><f ><k>STAFFCAREGORYID</k><v><![CDATA[4]]></v></f><f ><k>CHANGEREQUESTEDTO</k><v><![CDATA[0]]></v></f><f ><k>PRIVILEGECHANGE</k><v><![CDATA[Y]]></v></f><f ><k>REPORTID</k><v><![CDATA[8cbac45c79abdd4]]></v></f><f ><k>UPDATEDON</k><v><![CDATA[5/26/2009 6:20:27 PM]]></v></f><f ><k>ACTIVE</k><v><![CDATA[Y]]></v></f></physicanprofile>
 
 
<licensure><f ><k>PRACTITIONERID</k><v><![CDATA[1]]></v></f><f ><k>FROM</k><v><![CDATA[2009-2010]]></v></f><f ><k>TO</k><v><![CDATA[2010-2011]]></v></f><f ><k>STATELICENSE</k><v><![CDATA[N]]></v></f><f ><k>DEA</k><v><![CDATA[Y]]></v></f><f ><k>MALPRACTICEINSURANCE</k><v><![CDATA[N]]></v></f><f ><k>RESTICTIONS</k><v><![CDATA[Testing123]]></v></f><f ><k>MALPRACTICEINSURANCE</k><v><![CDATA[N]]></v></f><f ><k>UPDATEDON</k><v><![CDATA[5/26/2009 6:20:27 PM]]></v></f></licensure>
 
 
</practitionerdetails></qaworks>

Open in new window

file.txt
gpinfotechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Igor-KCommented:
The document structure does not match what you are trying to do with it.
Either change the document structure, to something like this:

declare @tab_Xml nvarchar(2000)
set @tab_Xml='<qaworks>
  <practitionerdetails id="wsiofy45ybyz0qel50vou045" created="5/26/2009 6:21:11 PM" createdby="??">
    <physicanprofile>
      <f ><k <NPI><![CDATA[NIP001]]></NPI><DEPARTMENTID><![CDATA[3]]></DEPARTMENTID><PRACTITIONERID><![CDATA[1]]></PRACTITIONERID>
      </k><v/></f>
    </physicanprofile>
  </practitionerdetails>
</qaworks>
'
DECLARE @idoc int
EXECUTE dbo.sp_xml_preparedocument @idoc  OUTPUT, @tab_Xml

select *
FROM OpenXml(@idoc,'/qaworks/practitionerdetails/physicanprofile/f/v',1)
 WITH     (PRACTITIONERID int    '/qaworks/practitionerdetails/physicanprofile/f/k/PRACTITIONERID',
           DEPARTMENTID int      '/qaworks/practitionerdetails/physicanprofile/f/k/DEPARTMENTID',
           NPI varchar(20)       '/qaworks/practitionerdetails/physicanprofile/f/k/NPI'
 )      


Or, If the SQL document is correct then you need to extract parameter name, value.  Then analyze the parameter name, for different parameter name do different coversion.
Something like this:


declare @tab_Xml nvarchar(2000)
set @tab_Xml='<qaworks>
  <practitionerdetails id="wsiofy45ybyz0qel50vou045" created="5/26/2009 6:21:11 PM" createdby="??">
    <physicanprofile>
      <f >
        <k>FROM1</k>
        <v><![CDATA[2009]]></v>
      </f>
      <f >
        <k>TO1</k>
        <v><![CDATA[2010]]></v>
      </f>
      <f >
        <k>PRACTITIONERNAME</k>
        <v><![CDATA[Villare, Robert MD]]></v>
      </f>
      <f >
        <k>PRACTITIONERID</k>
        <v><![CDATA[1]]></v>
      </f>
      <f >
        <k>LICENSE</k>
        <v><![CDATA[LIC001]]></v>
      </f>
      <f >
        <k>NPI</k>
        <v><![CDATA[NIP001]]></v>
      </f>
      <f >
        <k>DEPARTMENTNAME</k>
        <v><![CDATA[Department3]]></v>
      </f>
      <f >
        <k>DEPARTMENTID</k>
        <v><![CDATA[3]]></v>
      </f>
      <f >
        <k>STAFFCAREGORYNAME</k>
        <v><![CDATA[Category 4]]></v>
      </f>
      <f >
        <k>STAFFCAREGORYID</k>
        <v><![CDATA[4]]></v>
      </f>
      <f >
        <k>CHANGEREQUESTEDTO</k>
        <v><![CDATA[0]]></v>
      </f>
      <f >
        <k>PRIVILEGECHANGE</k>
        <v><![CDATA[Y]]></v>
      </f>
      <f >
        <k>REPORTID</k>
        <v><![CDATA[8cbac45c79abdd4]]></v>
      </f>
      <f >
        <k>UPDATEDON</k>
        <v><![CDATA[5/26/2009 6:20:27 PM]]></v>
      </f>
      <f >
        <k>ACTIVE</k>
        <v><![CDATA[Y]]></v>
      </f>
    </physicanprofile>
  </practitionerdetails>
</qaworks>'
DECLARE @idoc int
EXECUTE dbo.sp_xml_preparedocument @idoc  OUTPUT, @tab_Xml
create table #t(parameter_name nvarchar(2000),parameter_value nvarchar(2000))
insert into #t(parameter_name,parameter_value)
select param_name,param_value
FROM OpenXml(@idoc,'/qaworks/practitionerdetails/physicanprofile/f',1)
 WITH     (param_name nvarchar(2000)    'k',
           param_value nvarchar(2000)      'v'
 )      

select convert(int,parameter_value) as 'DEPARTMENTID' from #t where parameter_name='DEPARTMENTID'
select * from #t
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brejkCommented:
If it's SQL Server 2005 you can use xml data type and its methods. The code below should give you some idea on how to use nodes() and value() methods to get a table result set from your XML document.
DECLARE @tab_Xml xml
SET @tab_Xml = '<qaworks>
<practitionerdetails id="wsiofy45ybyz0qel50vou045" created="5/26/2009 6:21:11 PM" createdby="??">
<physicanprofile>
<f><k>FROM1</k><v><![CDATA[2009]]></v></f>
<f><k>TO1</k><v><![CDATA[2010]]></v></f>
<f ><k>PRACTITIONERNAME</k><v><![CDATA[Villare, Robert MD]]></v></f>
<f ><k>PRACTITIONERID</k><v><![CDATA[1]]></v></f>
<f ><k>LICENSE</k><v><![CDATA[LIC001]]></v></f>
<f ><k>NPI</k><v><![CDATA[NIP001]]></v></f>
<f ><k>DEPARTMENTNAME</k><v><![CDATA[Department3]]></v></f>
<f ><k>DEPARTMENTID</k><v><![CDATA[3]]></v></f>
<f ><k>STAFFCAREGORYNAME</k><v><![CDATA[Category 4]]></v></f>
<f ><k>STAFFCAREGORYID</k><v><![CDATA[4]]></v></f>
<f ><k>CHANGEREQUESTEDTO</k><v><![CDATA[0]]></v></f>
<f ><k>PRIVILEGECHANGE</k><v><![CDATA[Y]]></v></f>
<f ><k>REPORTID</k><v><![CDATA[8cbac45c79abdd4]]></v></f>
<f ><k>UPDATEDON</k><v><![CDATA[5/26/2009 6:20:27 PM]]></v></f>
<f ><k>ACTIVE</k><v><![CDATA[Y]]></v></f>
</physicanprofile> 
<licensure>
<f ><k>PRACTITIONERID</k><v><![CDATA[1]]></v></f>
<f ><k>FROM</k><v><![CDATA[2009-2010]]></v></f>
<f ><k>TO</k><v><![CDATA[2010-2011]]></v></f>
<f ><k>STATELICENSE</k><v><![CDATA[N]]></v></f>
<f ><k>DEA</k><v><![CDATA[Y]]></v></f>
<f ><k>MALPRACTICEINSURANCE</k><v><![CDATA[N]]></v></f>
<f ><k>RESTICTIONS</k><v><![CDATA[Testing123]]></v></f>
<f ><k>MALPRACTICEINSURANCE</k><v><![CDATA[N]]></v></f>
<f ><k>UPDATEDON</k><v><![CDATA[5/26/2009 6:20:27 PM]]></v></f>
</licensure>
</practitionerdetails>
</qaworks>'
 
SELECT
  T.c.value('(physicanprofile/f[k[1] eq "FROM1"]/v)[1]','int') AS physicanprofile_from1,
  T.c.value('(physicanprofile/f[k[1] eq "PRACTITIONERNAME"]/v)[1]','varchar(50)') AS physicanprofile_practitionername,
  T.c.value('(physicanprofile/f[k[1] eq "UPDATEDON"]/v)[1]','datetime') AS physicanprofile_updatedon  
FROM @tab_Xml.nodes('/qaworks/practitionerdetails') AS T(c)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.