October 7, 2016 at 6:47 am
Hi All,
My xml File is below struture.
<patientRequest>
<invocationPoint>RequestDetails</invocationPoint>
<requestID>000004</requestID>
<requestType>XX</requestType>
<claimNumber />
<SourceSystemId>Source</SourceSystemId>
<overallRequestStatus code="XXXXXX">Approved</overallRequestStatus>
<treatmentSetting code="00">' XXXXXXXXX</treatmentSetting>
<member>
<memberID>12345678</memberID>
</member>
<facility>
<Id>1234</Id>
</patientRequest>
I am trying to do something like this with Rowxml
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = "Above XML"
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/PatientRequest/treatmentSetting',2)
WITH
(
requestID [varchar](500) '../requestID',
requestType [varchar](100) '../requestType',
SourceSystemId [varchar](50) '../SourceSystemId',
overallRequestStatus [varchar](100)'../overallRequestStatus',
treatmentSetting [varchar](10) '@code'
)
EXEC sp_xml_removedocument @hDoc
I get proper result with this. But If I want member then I am not able to get the value.
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = "Above XML"
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/PatientRequest/treatmentSetting/member',2)
WITH
(
requestID [varchar](500) '../../requestID',
requestType [varchar](100) '../../requestType',
SourceSystemId [varchar](50) '../../SourceSystemId',
overallRequestStatus [varchar](100)'../../overallRequestStatus',
treatmentSetting [varchar](10) '../@code',
memberID [varchar](10) 'memberID'
)
Where am I going wrong? I want all the details in one table.
Any help is appreciated!!
Thanks
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply