XML help

Question

XML help

Guras

SSCertifiable

Points: 6518
More actions
July 26, 2011 at 2:50 pm

#241456

I need to query data and write it out in an XML file.
I am calling a sp from a .NET program and then will be using the dataAdapter to write to the file. My xml document should be like below
<claimRequest>
<target>
<identifier>Patient1</identifier>
<Type> Rcv</type>
</target>
<comment>
<code>Code1</code>
<Text> Test comment</text>
<code>Code2</code>
<Text>Test coment2</Text>
</comment>
<target>
<identifier>Patient2</identifier>
<Type> Rcv</type>
</target>
<comment>
<code>Code10</code>
<Text> Test2 comment1</text>
<code>Code2</code>
<Text>Test2 coment2</Text>
</comment>
</claimRequest>
I have the following query
Declare @DTBEGIN datetime
declare @DTEND datetime
SET @DTBEGIN = '07/24/2011'
SET @DTEND = '07/26/2011'
SELECT ( SELECT p.Account AS 'identifier',
'Rcv' AS Type
From hcfsdatabase.dbo.Patient_Table p
INNER JOIN hcfsdatabase.dbo.comments c
ON C.PTNO = P.PTNO
WHERE LEFT(C.COMMENT_CODE,1) in (1)
AND CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN @DTBEGIN AND @DTEND
AND p.HOSPITALCODE = 333
FOR
XML PATH('Target'),
TYPE
),
( SELECT '' AS 'Code',
c.comment AS 'Text'
From hcfsdatabase.dbo.Patient_Table p
INNER JOIN hcfsdatabase.dbo.comments c
ON C.PTNO = P.PTNO
WHERE LEFT(C.COMMENT_CODE,1) in (1)
AND CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN @DTBEGIN AND @DTEND
AND p.HOSPITALCODE = 333
FOR
XML PATH('Comment'),
TYPE
)FOR XML PATH(''),
ROOT('claimTrackingRequest')
GO
The above query is not outputting in the format I would like. It's listing all teh patient first and then the comments.can you help me?
Any help will be greatly appreciated!
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 1

Can you include Create Table statements and Insert statements with sample data? It would help us come up with a solution that works for you without it being broken.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Guras SSCertifiable Points: 6518 More actions · Answer 2

I got it working this far....but still need help for one more step

CREATE TABLE #TEMPTBLCNA

(

RECORD_NUM INT NULL,

C1 VARCHAR(1) NULL,

COMMENT_CODE VARCHAR(100) NULL,

STATUS_CODE INT NULL,

STATUS_DESC VARCHAR(200) NULL,

COMMENT VARCHAR(1500) NULL,

ACCOUNT VARCHAR(12) NULL,

HOSPITAL INT NULL,

DATEENTERED DATETIME NULL,

PTNO INT NULL,

NOTEID INT IDENTITY(1,1)

)

INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)

SELECT MIN(C.RECORD_NUM),

LEFT(COMMENT_CODE,1),

C.COMMENT_CODE,

LEFT(C.COMMENT_CODE, 3),

m.[STATUS],

c.COMMENT,

RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),

p.HOSPITAL,

CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),

P.PTNO

FROM COMMENTS C INNER JOIN PATIENT_TABLE P

ON C.PTNO = P.PTNO

INNER JOIN MasterCodeList m

ON m.NewCode = LEFT(C.COMMENT_CODE, 3)

WHERE (C.COMMENT_CODE like ('1%') or

C.COMMENT_CODE like ('2%') or

C.COMMENT_CODE like ('3%') or

C.COMMENT_CODE like ('4%') or

C.COMMENT_CODE like ('5%') or

C.COMMENT_CODE like ('8%') )

AND CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'

AND P.HOSPITAL IN (123, 234)

GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]

ORDER BY P.PTNO

SELECT c.ACCOUNT as 'identifier', 'RCV' as 'Type',

( SELECT '' AS 'Code',

a.comment AS 'Text'

FROM #TEMPTBLCNA a

WHERE a.RECORD_NUM = c.RECORD_NUM

FOR XML PATH(''), TYPE) AS 'Comment'

FROM #TEMPTBLCNA c

FOR XML PATH('Target'), ROOT('claimTrackingRequest')

The result of the above is :

<Text>07/29 - comment test1 </Text>

</Comment>

</Target>

<Text>07/25 - comment test1 </Text>

</Comment>

</Target>

<Text>07/26 - comment test2 </Text>

</Comment>

</Target>

<Text>07/26 - comment test3 </Text>

</Comment>

</Target>

</claimTrackingRequest>

But I wanted the result like this

<Text>07/29 - comment test1 </Text>

</Comment>

</Target>

<Text>07/25 - comment test1</Text>

</Comment>

<Text>07/26 - comment test2 </Text>

</Comment>

<Text>07/26 - comment test2 </Text>

</Comment>

</Target>

</claimTrackingRequest>

Basically, I do not want the account number to be repeated if it had multiple comments.

Any help would be greatly appreciated!

Guras SSCertifiable Points: 6518 More actions · Answer 3

SELECT c.ACCOUNT as 'identifier', 'RCV' as 'Type',

( SELECT '' AS 'Code',

a.comment AS 'Text'

FROM #TEMPTBLCNA a

WHERE a.ptno = c.ptno

FOR XML PATH(''), TYPE) AS 'Comment'

FROM #TEMPTBLCNA c

group by c.ptno,c.ACCOUNT

FOR XML PATH('Target'), ROOT('claimTrackingRequest')

I got it working finally, thought I would post it here, may be will be useful for somebody..someday. 🙂

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 4

Thanks for posting the solution. It's always good to know what works and what doesn't.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.