November 17, 2014 at 11:42 am
Hi
I am very new to xml , i want to parse an xml .
I have been trying to do this but getting the following error , Please let me know where I am going wrong .
declare @x xml
set @x = '<CLAIM version="native">
<INPUT>
<HEADER CLM_ID="130421E02595" FEE_ID="3" PROV_TYPE="PAR" BIRTH_DT="05101949" SEX="F" ZIP="283748708" LTR_D="0" COV_D="0" ICD_VER="9" GROUP="2" NPI="1518914514" BENEFICIARY_ZIP="28376" TOTAL_AMT="10.2400">
<DIAG_CODES>
<DX CODE="786.05" />
</DIAG_CODES>
</HEADER>
<LINES>
<LINE CLM_ID="130421E02595" LINE_NUM="01" LIDOS="04142013" HCPCS="71020" MOD_01="26" UNITS="1" CHARGES="39" POS="23" ALLOWANCE="10.24" />
</LINES>
</INPUT>
<OUTPUT>
<PRICER PROCESSED="1" PROCESS_DURATION="47">
<HEADER LOC="North Carolina" TOO_MANY="0" PROV_TYPE="PAR" PROCESS_TIME="06/06/2013 10:06:42" OSCAR="000000">
<WARNINGS>
<WARNING CODE="WC-50095" TEXT="Invalid Fee ID" />
</WARNINGS>
<INFO>
<ITEM KEY="Provider Type" VALUE="Physician (Medicare Participating)" />
<ITEM KEY="Locality" VALUE="North Carolina" />
</INFO>
</HEADER>
<LINES>
<LINE RBRVS_TRVU="0.31" RBRVS_STATUS="A" RBRVS_GLOB="XXX" RBRVS_STATUS_DESC="Active code" RBRVS_ASSTSURG="0" RBRVS_ASSTSURG_PERCENT="0.16" RBRVS_BILAT="0" RBRVS_DIFI="99" RBRVS_ENDO_FAMILY_GROUP="0" RBRVS_INTRAOP="0" RBRVS_MPR_RATE="10.24" RBRVS_MULT="0" RBRVS_PC0="10.24" RBRVS_PE="0.07416" RBRVS_PREOP="0" BRS_CB_DESC="RBRVS" POS_TYPE="F" PAY_LIMIT_CHRG="0" HCPCS_DESCRIPTION="Chest x-ray" HCPCS_NARRATIVE="n/a" RBRVS_PC="10.24" RBRVS_TC="18.84" BRS_CB="1" BETOS="I1A" RBRVS_CF="34.023" RBRVS_TC0="18.84" ASC_SERVICE="0" EDIT_CODING_FLAG="0" EDIT_CODING_MODIFIER="0" PHY_BONUS_TOTAL="0" BRS_HCPCS_MATCH="333" CMS_ALLOW_PRE_CAP="10.24">
<REMARKS>
<REMARK CODE="RL-50088" TEXT="Allowance available" />
</REMARKS>
</LINE>
</LINES>
</PRICER>
</OUTPUT>
</CLAIM>
'
SELECT
x.HEADER.value('@clm_id[1]', 'NVARCHAR(max)') AS CODE
FROM @x.nodes('//INPUT/HEADER') AS x(item)
ERROR :
Msg 207, Level 16, State 1, Line 40
Invalid column name 'HEADER'.
November 17, 2014 at 1:43 pm
you want:
SELECT
x.item.value('@clm_id[1]', 'NVARCHAR(max)') AS CODE
FROM @x.nodes('//INPUT/HEADER') AS x(item)
since you called the nodes column 'item'
Gerald Britton, Pluralsight courses
November 17, 2014 at 1:52 pm
I changed it to
SELECT
x.item.value('@clm_id[1]', 'NVARCHAR(max)') AS CODE
FROM @x.nodes('//INPUT/HEADER') AS x(item)
But getting data as NULL
November 17, 2014 at 1:56 pm
Fund the solution used this
SELECT
header.value('@CLM_ID', 'NVARCHAR(MAX)') as CLM_ID,
header.value('@PROV_TYPE', 'VARCHAR(20)') as PROV_TYPE,
header.value('@ICD_VER', 'INT') as ICD_VERSION,
header.value('@SEX','VARCHAR(2)') as SEX,
header.value('@NPI','INT') as NPI,
header.value('@TOTAL_AMT', 'NVARCHAR(MAX)') as TOTAL_AMT
--HEADER.value('@HCPCS','NVARCHAR(MAX)') AS HPCS
FROM @xml.nodes('CLAIM/INPUT/HEADER')e(HEADER)
November 17, 2014 at 2:03 pm
XML is case sensitive, so it can't match the attribute you are searching for.
try:
SELECT
x.item.value('@CLM_ID[1]', 'NVARCHAR(max)') AS CODE
FROM @x.nodes('//INPUT/HEADER') AS x(item)
Gerald Britton, Pluralsight courses
November 17, 2014 at 2:35 pm
Quick thought, don't use the traversing // if the position of the element is known, far more expensive than the direct reference.
😎
SELECT
x.item.value('@CLM_ID', 'NVARCHAR(max)') AS CODE
FROM @x.nodes('CLAIM/INPUT/HEADER') AS x(item)
November 17, 2014 at 2:55 pm
Hi
Got an other issue how do i get to the node Line , use d a OUTERAPPLY but the data is showing null
SELECT
x.item.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,
g.line.value('@CLM_ID','NVARCHAR(MAX)') as CLM_IDLine
FROM @x.nodes('CLAIM/INPUT/HEADER') AS x(item)
outer apply @x.nodes('CLAIM/INPUT/LINES') AS g(line)
November 17, 2014 at 3:03 pm
nikki123 (11/17/2014)
HiGot an other issue how do i get to the node Line , use d a OUTERAPPLY but the data is showing null
SELECT
x.item.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,
g.line.value('@CLM_ID','NVARCHAR(MAX)') as CLM_IDLine
FROM @x.nodes('CLAIM/INPUT/HEADER') AS x(item)
outer apply @x.nodes('CLAIM/INPUT/LINES') AS g(line)
Has to be the full path, this should get you passed this hurdle
😎
SELECT
HEADER.DATA.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,
LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine
FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)
OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)
OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)
November 18, 2014 at 7:16 am
how do i get through the diag codes was working like this but shows null value
SELECT
HEADER.DATA.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,
LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,
DIAG.DATA.value('@DX_code','NVARCHAR(MAX)') as DX
FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)
OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)
OUTER APPLY INPUT.DATA.nodes('DIAG_CODES/DX') AS DIAG(DATA)
OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)
November 18, 2014 at 7:35 am
nikki123 (11/18/2014)
how do i get through the diag codes was working like this but shows null valueSELECT
HEADER.DATA.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,
LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,
DIAG.DATA.value('@DX_code','NVARCHAR(MAX)') as DX
FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)
OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)
OUTER APPLY INPUT.DATA.nodes('DIAG_CODES/DX') AS DIAG(DATA)
OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)
Correct the attribute name, should be @CODE not @DX_code
😎
November 18, 2014 at 7:42 am
SELECT
HEADER.DATA.value('@CLM_ID', 'NVARCHAR(max)') AS CODE,
LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,
DIAG.DATA.value('@CODE','NVARCHAR(MAX)') as DX
FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)
OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)
OUTER APPLY INPUT.DATA.nodes('DIAG_CODES/DX') AS DIAG(DATA)
OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)
but still gives me DX as NUll
CODECLM_IDLineDX
130421E06639130421E06639NULL
November 18, 2014 at 8:14 am
got it was using the wrong node
SELECT
HEADER.DATA.value('@CLM_ID', 'nvarchar(max)') AS CODE,
LINE.DATA.value('@CLM_ID','NVARCHAR(MAX)') AS CLM_IDLine,
DIAG.DATA.value('@CODE','DECIMAL(18,1)') as DX
FROM @x.nodes('CLAIM/INPUT') AS INPUT(DATA)
OUTER APPLY INPUT.DATA.nodes('HEADER') AS HEADER(DATA)
outer APPLY HEADER.DATA.nodes('DIAG_CODES/DX') AS DIAG(DATA)
OUTER APPLY INPUT.DATA.nodes('LINES/LINE') AS LINE(DATA)
Thnaks all for you help
December 1, 2014 at 1:35 pm
R:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply