XML In Temp Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

XML In Temp Table

Author

Topic

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-01 : 01:34:28

Hi friends firs copy all xml and select statement and check
I am getting some null values in my first table how to add condition to check if null then it should not update Or any other way to avoid.

DECLARE @LV_XML XML
DECLARE @LV_XML_HND INT,@LV_ID_VAL INT
DECLARE @IPARAM3 INT=229
DECLARE @LV_SYS_DT DATETIME
SET @LV_SYS_DT=GETDATE()
SET @LV_XML=
'
<EdiXml>
<Coverage Id="1">
<Heading ResponseId="017301990" ParticipentId="RXHUB" ReceiverId="T00000000021041" ResponseDateTime="05/31/2011 06:12" />

<Source PBMName="RXHUBPBM" PBMId="T00000000001000" />

<Receiver ProviderId="4321012352" FirstName="Mark" LastName="Jones" Suffix="MD">
<Reference Code="EO" Name="Submitter Identification Number" Value="T00000000021041" />
</Receiver>

<Subscriber FirstName="BERT" LastName="SCHNUR" MiddleName="L" RelationshipDescription="Dependent" AddressLine1="312 HILL ROAD" City="HILLSBORO" State="MO" Country="US" PostalCode="63050" DateOfBirth="04/19/1945" GenderCode="M" GenderDescription="Male">
<Reference Code="49" Name="Family Unit Number" Value="01" />
<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" PlanName="PLANX" ServiceTypeCode="30" ServiceTypeCodeDescription="Health Benefit Plan Coverage">
<DTP Code="291" Description="Plan" DateTimePeriod="08/01/2010-12/31/2099" DtpQualifier="RD8" />
<Reference Code="18" Name="Plan Number" Value="1234" />
<Reference Code="6P" Name="Group Number" Value="TTC1" GroupName="TEST MATCH 1" />
<Reference Code="FO" Name="Drug Formulary Number" Value="FSL102" />
<Reference Code="CLI" Name="Coverage List ID" Value="COV102" />
<Reference Code="N6" Name="Plan Network Identification Number" Value="610415" />
<Reference Code="IG" Name="Insurance Policy Number" Value="COP102" />
</Eligibility>

<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" ServiceTypeCode="88" ServiceTypeCodeDescription="Retail Pharmacy" />

<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" ServiceTypeCode="90" ServiceTypeCodeDescription="Mail Order Pharmacy" />
</Subscriber>
</Coverage>

<Coverage Id="2">
<Source PBMName="RXHUBPBM" PBMId="T00000000001000" />
<Receiver ProviderId="4321012352" FirstName="Mark" LastName="Jones" Suffix="MD">
<Reference Code="EO" Name="Submitter Identification Number" Value="T00000000021041" />
</Receiver>
<Subscriber RelatioshipDescription="Dependent">
<Eligibility EligibilityCode="6" EligibilityCodeDescription="Inactive" ServiceTypeCode="30" ServiceTypeCodeDescription="Health Benefit Plan Coverage">
<DTP Code="291" Description="Plan" DateTimePeriod="08/01/2010" DtpQualifier="D8" />
</Eligibility>
</Subscriber>
</Coverage>

<Coverage Id="3">
<Source PBMName="RXHUBPBM" PBMId="T00000000001000" />
<Receiver ProviderId="4321012352" FirstName="Mark" LastName="Jones" Suffix="MD">
<Reference Code="EO" Name="Submitter Identification Number" Value="T00000000021041" />
</Receiver>
<Subscriber FirstName="BERT" LastName="SCHNUR" MiddleName="L" RelationshipCode="18" RelationshipDescription="Self" AddressLine1="312 HILL ROAD" City="HILLSBORO" State="MO" Country="US" PostalCode="63050" DateOfBirth="04/19/1945" GenderCode="M" GenderDescription="Male">
<Reference Code="HJ" Name="Identification Card Number" Value="111111116" />
<Reference Code="49" Name="Family Unit Number" Value="001" />

<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" PlanName="PLAN101" ServiceTypeCode="30" ServiceTypeCodeDescription="Health Benefit Plan Coverage">
<Reference Code="6P" Name="Group Number" Value="G000000" GroupName="HONEYWELL" />
<Reference Code="FO" Name="Drug Formulary Number" Value="FSL101" />
<Reference Code="ALS" Name="Alternative List ID" Value="ALT101" />
</Eligibility>

<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" PlanName="RTL101" ServiceTypeCode="88" ServiceTypeCodeDescription="Retail Pharmacy">
<DTP Code="291" Description="Plan" DateTimePeriod="08/01/2010-12/31/2099" DtpQualifier="RD8" />
</Eligibility>

<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" PlanName="LTC101">
<DTP Code="291" Description="Plan" DateTimePeriod="05/01/2010-12/31/2099" DtpQualifier="RD8" />
</Eligibility>

<Eligibility EligibilityCode="1" EligibilityCodeDescription="Active Coverage" ServiceTypeCode="90" ServiceTypeCodeDescription="Mail Order Pharmacy" />

</Subscriber>
</Coverage>

</EdiXml>
'

--DECLARE @LV_ID INT=1
--DECLARE @LV_ID_MAX INT

--SELECT @LV_ID_MAX=@LV_XML.value('count(//Coverage/Source)', 'int')
--SELECT @LV_ID_MAX

--WHILE @LV_ID<=@LV_ID_MAX
--BEGIN
-- SET @LV_XML.modify('
-- insert attribute SrcId {sql:variable("@LV_ID") } into
-- (//Coverage/Source [position()=sql:variable("@LV_ID")])[1]');
-- SET @LV_ID=@LV_ID+1
--END
--SELECT @LV_XML
EXEC sp_xml_preparedocument @LV_XML_HND OUTPUT, @LV_XML

--SELECT RespId,RespDT INTO #MT_ELGBLTY_HDR FROM
--OPENXML(@LV_XML_HND,'EdiXml/Coverage/Heading',2)WITH (RespId VARCHAR(20) '@ResponseId',RespDT DATETIME '@ResponseDateTime')

--INSERT INTO dbo.MS188_ELG_REQ_MAST(PATIENT_NUM,RESP_ID,RESP_DT,RESP_ACTIVE_FLG,SUB_SAME_AS_PAT,CREATED_BY,CREATED_DT)
--SELECT @LV_PATIENT_NUM,RespId,RespDT,1,1,@IPARAM3,@LV_SYS_DT FROM #MT_ELGBLTY_HDR
--SELECT @LV_REQ_ID=@@IDENTITY

----INSERT INTO dbo.MS189_PBM_RESP_MAST(REQUEST_ID,SOURCE_ID,PBM_PRVSUB_ID,LNAME,FNAME,MNAME,SUFFIX,PREFIX,ADD1,ADD2,CITY,STATE_CODE,ZIP_CODE,COUNTRY,COUNTRY_SUB_DIV,DATE_OF_BIRTH,GENDER,RELSHIP,PBM_PRVSUB_FLG,DTP_CODE,DTP_DESC,CREATED_BY,CREATED_DT)
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID,PBMID PBM_PRVSUB_ID,PBMName LNAME,NULL FNAME,NULL MNAME,NULL SUFFIX,NULL PREFIX,NULL ADD1,NULL ADD2,NULL CITY,NULL STATE_CODE,NULL ZIP_CODE,
NULL COUNTRY,NULL COUNTRY_SUBDIV_CD,NULL DOB,NULL GENDER_CD,NULL GENDER,NULL RELSHIP_CD,NULL RELSHIP,'P' PBM_PRVSUB_FLG,NULL DTP_CODE,NULL DTP_DESC,@IPARAM3 CREATED_BY,@LV_SYS_DT CREATED_DT
FROM OPENXML(@LV_XML_HND,'//Coverage/Source',2)WITH (PBMName VARCHAR(20) '@PBMName',PBMID VARCHAR(30) '@PBMId', SRC_ID INT '../@Id')
UNION ALL /*Receiver(Prescriber/Provider) Details*/
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID,ProviderId,RLname,RFname,RMname,RSuffix,RPrefix,RAdd,NULL,RCity,RState,RZip,RCntry,RCsdc,NULL DOB,NULL GenderCode,NULL GENDER,NULL RELSHIP_CD,NULL RELSHIP,'R' PBM_PRVSUB_FLG,NULL,NULL,@IPARAM3 CREATED_BY,@LV_SYS_DT CREATED_DT
FROM OPENXML(@LV_XML_HND,'//Coverage/Receiver',2)WITH (SRC_ID INT '../@Id',
ProviderId VARCHAR(30) '@ProviderId',RLname VARCHAR(35) '@LastName',RFname VARCHAR(35) '@FirstName',
RMname VARCHAR(35) '@MiddleName',RSuffix VARCHAR(10) '@Suffix',RPrefix VARCHAR(10) '@Prefix',RAdd VARCHAR(200) '@AddressLine1',RCity VARCHAR(60) '@City',RState VARCHAR(5) '@State',RZip VARCHAR(10) '@PostalCode',RCntry VARCHAR(30) '@Country',RCsdc VARCHAR(20) '@CountrySubDivisionCode')
UNION ALL /*Subscriber Details(Patient)*/
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID, SubId,SLname,SFname,SMname,NULL SUFFIX,NULL PREFIX,SAdd,NULL SAdd2,SCity,SState,SZip,SCntry,NULL SCntrySubDivCd,SDOB,GenderCode GENDER_CD,SGender,RELSHIP_CD,SRel,'S' PBM_PRVSUB_FLG,DTpc,DTpdesc,@IPARAM3 CREATED_BY,@LV_SYS_DT CREATED_DT
FROM OPENXML(@LV_XML_HND,'//Coverage/Subscriber',2)WITH (SRC_ID INT '../@Id',
SubId VARCHAR(5) '@SubscriberId',SLname VARCHAR(35) '@LastName',SFname VARCHAR(35) '@FirstName',
SMname VARCHAR(35) '@MiddleName',SAdd VARCHAR(200) '@AddressLine1',SCity VARCHAR(60) '@City',SState VARCHAR(5) '@State',SZip VARCHAR(10) '@PostalCode',SCntry VARCHAR(30) '@Country',
SDOB VARCHAR(10) '@DateOfBirth',RELSHIP_CD VARCHAR(60) '@RelationshipCode' ,SRelDesc VARCHAR(60) '@RelationshipDescription',GenderCode VARCHAR(10) '@GenderCode',SGender VARCHAR(10) '@GenderDescription',SRel VARCHAR(60) '@RelationshipDescription',
DTpc VARCHAR(20) '//Coverage/Subscriber/DateTimePeriod/@Code',DTpdesc VARCHAR(20) '//Coverage/Subscriber/DateTimePeriod/@Description')
UNION ALL /*Dependent Details*/
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID,SubId,SLname,SFname,SMname,NULL,NULL,SAdd,NULL SAdd2,SCity,SState,SZip,SCntry,NULL SCntrySubDivCd,SDOB,GenderCode GENDER_CD,SGender,RELSHIP_CD,SRel,'D' PBM_PRVSUB_FLG,DTpc,DTpdesc,@IPARAM3 CREATED_BY,@LV_SYS_DT CREATED_DT
FROM OPENXML(@LV_XML_HND,'//Coverage/Dependent',2)WITH (SRC_ID INT '../../@Id',
SubId VARCHAR(5) '@SubscriberId',SLname VARCHAR(35) '@LastName',SFname VARCHAR(35) '@FirstName',
SMname VARCHAR(35) '@MiddleName',SAdd VARCHAR(200) '@AddressLine1',SCity VARCHAR(60) '@City',SState VARCHAR(5) '@State',SZip VARCHAR(10) '@PostalCode',SCntry VARCHAR(30) '@Country',
SDOB VARCHAR(10) '@DateOfBirth',RELSHIP_CD VARCHAR(60) '@RelationshipCode',SRelDesc VARCHAR(60) '@RelationshipDescription',GenderCode VARCHAR(10) '@GenderCode',SGender VARCHAR(10) '@GenderDescription',SRel VARCHAR(60) '@RelationshipDescription',
DTpc VARCHAR(20) '//Coverage/Dependent/DTP/@Code',DTpdesc VARCHAR(20) '//Coverage/Dependent/DTP/@Description')
ORDER BY SRC_ID

----INSERT INTO dbo.MS190_ELG_RESP_DTLS(REQUEST_ID,SOURCE_ID,ELGBLTY_SUB_FLG,ELGBLTY_REF_NAME,ELGBLTY_REF_VALUE,ELGBLTY_REF_DESC,ELGBLTY_REF_QLF,ELGBLTY_CODE,ELGBLTY_DESC,HEALTH_PLAN,SRVC_TYPE_CODE,SRVC_TYPE_DESC,DTP_CODE,DTP_DESC,PHRMCY_ID,DTP_PERIOD,CREATED_BY,CREATED_DT)
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID,'E' ELG_SUB_FLG,ELG_NAME,ELG_VALUE,ELG_REF_DESC,REF_ID_QLF_CODE,ELG_CD,ELG_CD_DESC,HPN,ELG_STC,ELG_STC_DESC,DTP_CD,DTP_DESC,DTP_PERIOD,DTP_QLF,@IPARAM3 CREATED_BY,@LV_SYS_DT CREATED_DT
FROM OPENXML(@LV_XML_HND,'//Coverage/Subscriber/Eligibility/Reference',2) WITH (SRC_ID VARCHAR(20) '../../../@Id',ELG_NAME VARCHAR(60) '@Name',ELG_REF_DESC VARCHAR(255) '@GroupName',
ELG_VALUE VARCHAR(20) '@Value',REF_ID_QLF_CODE VARCHAR(20) '@Code',ELG_CD VARCHAR(10) '../@EligibilityCode',ELG_CD_DESC VARCHAR(60) '../@EligibilityCodeDescription',
ELG_CVRG_LVL_CD VARCHAR(60) '../@CoverageLevelCode',ELG_CVRG_LVL_DESC VARCHAR(60) '../@CoverageLevelDescription',HPN VARCHAR(60) '../@PlanName',ELG_STC VARCHAR(60) '../@ServiceTypeCode',ELG_STC_DESC VARCHAR(60) '../@ServiceTypeCodeDescription',
DTP_CD VARCHAR(20) '../DTP/@Code',DTP_DESC VARCHAR(80) '../DTP/@Description',DTP_PERIOD VARCHAR(80) '../DTP/@DateTimePeriod',DTP_QLF VARCHAR(80) '../DTP/@DtpQualifier')
UNION ALL
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID,'S' ELG_SUB_FLG,SUB_REF_NAME,SUB_REF_VALUE,NULL,SUB_REF_CODE,NULL,NULL,NULL,NULL,NULL,DTP_CD,DTP_DESC,DTP_PERIOD,DTP_QLF,@IPARAM3,@LV_SYS_DT FROM OPENXML(@LV_XML_HND,'//Coverage/Subscriber/Reference',2)
WITH(SRC_ID VARCHAR(20) '../../@Id',SUB_REF_NAME VARCHAR(80) '@Name', SUB_REF_VALUE VARCHAR(80) '@Value',SUB_REF_CODE VARCHAR(80) '@Code',DTP_CD VARCHAR(20) './DTP/@Code',DTP_DESC VARCHAR(80) './DTP/@Description',DTP_PERIOD VARCHAR(80) './DTP/@DateTimePeriod',DTP_QLF VARCHAR(80) './DTP/@DtpQualifier')
UNION ALL
SELECT /*@LV_REQ_ID REQ_ID,*/SRC_ID,'R' ELG_SUB_FLG,SUB_REF_NAME,SUB_REF_VALUE,NULL,SUB_REF_CODE,NULL,NULL,NULL,NULL,NULL,DTP_CD,DTP_DESC,DTP_PERIOD,DTP_QLF,@IPARAM3,@LV_SYS_DT FROM OPENXML(@LV_XML_HND,'//Coverage/Receiver/Reference',2)
WITH(SRC_ID VARCHAR(20) '../../@Id',SUB_REF_NAME VARCHAR(80) '@Name', SUB_REF_VALUE VARCHAR(80) '@Value',SUB_REF_CODE VARCHAR(80) '@Code',DTP_CD VARCHAR(20) './DTP/@Code',DTP_DESC VARCHAR(80) './DTP/@Description',DTP_PERIOD VARCHAR(80) './DTP/@DateTimePeriod',DTP_QLF VARCHAR(80) './DTP/@DtpQualifier')

EXEC sp_xml_removedocument @LV_XML_HND

--SELECT * FROM #MT_ELGBLTY_HDR
--SELECT * FROM #RESP_MAST
--SELECT * FROM #RESP_MAST_DTLS

--DROP TABLE #MT_ELGBLTY_HDR

In Love... With Me!

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-01 : 11:41:50

This might be some use to you it's a procedure I use to take an xml document and use it to do inserts to a table:

CREATE PROCEDURE [dbo].[INS_FromXML]
(
@XMLInput XML
)
AS
;WITH xmlCTE AS (
--each node in xmldoc becomes a column in cte, replace @n1 with your node names
SELECT
x.h.value('@n1', 'VARCHAR(11)') AS n1,
x.h.value('@n2', 'char(1)') AS n2,
x.h.value('@n3', 'VARCHAR(4)') AS n3

FROM @XMLInput.nodes('/root/row') AS x(h)
)
MERGE dbo.MyTable AS Target
USING xmlCTE AS Source
ON (Target.n1 = Source.n1 )
--No Matched - just do inserts no updates
WHEN NOT MATCHED THEN
INSERT (n1 , n2, n3

)
VALUES (Source.n1, Source.n2, Source.n3)
;

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources