[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

insert or update using xml data

Posted on 2009-06-30
2
Medium Priority
?
282 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:gpinfotech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
Igor-K earned 1500 total points
ID: 24744405
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
 
LVL 18

Expert Comment

by:brejk
ID: 24744720
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question