Count records in XML File - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Count records in XML File

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-27 : 11:06:25

<BACSDocument>
<Data>
<MessagingAdvices>
<MessagingHeader document-number="0" advice-type="ADDACS" />
<AddresseeInformation name=" " />
<MessagingAdvice user-number="111111" record-type="A" effective-date="2014-02-17"/>
<MessagingAdvice user-number="111111" record-type="A" effective-date="2014-02-17/>
<MessagingError />
</MessagingAdvices>
</Data>
</BACSDocument>

ALTER PROCEDURE [dbo].[xml_Import_User_ADDACS]
@NewADDACSTable NewADDACSTable READONLY
AS
BEGIN

DECLARE @SQL NVARCHAR(4000) = NULL
DECLARE @PARMS NVARCHAR(1000) = NULL

CREATE TABLE #WORKINGXML
(
XMLTEXT XML
)

/* Load XML into temporary table */
INSERT INTO #WORKINGXML
SELECT XMLTEXT FROM @NewADDACSTable

SELECT
NULL,
h.c.value('@user-number[1]' , 'VARCHAR(6)') ,
CONVERT( SMALLDATETIME,h.c.value('@report-generation-date[1]' , 'VARCHAR(20)'))
FROM ( SELECT XMLTEXT AS Xmlreport FROM @NewADDACSTable) d
CROSS APPLY Xmlreport.nodes('//Data/MessagingAdvices') AS a(c)
OUTER APPLY a.c.nodes('MessagingHeader') h(c)

SELECT * FROM #WORKINGXML
DROP TABLE #WORKINGXML

END

I would like to get the count of MessagingAdvice, so in this instance I should get the following;
270306 2014-02-17 00:00:00 2

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-27 : 12:10:42

I didn't quite get where the 270306 in your sample results came from. Also, since user-number is an attribute in the MessagingAdvice node, when you get the user-number and the count, and if the user-number happens to be different, what do you want to get? The count grouped by user-number, or just a total count under a given MessagingHeader? In any case you can use the count function like in the example below.

SELECT  c.query('count(MessagingAdvice)') AS N
FROM 
	#WORKINGXML AS T1
	CROSS APPLY XMLTEXT.nodes('//Data/MessagingAdvices') AS a(c)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-27 : 13:04:04

I solved it this way:


select count(*) [user-numbers], [effective-date] from (
	SELECT NULL
		 , h.c.value('@user-number[1]' , 'VARCHAR(6)')
		 , CONVERT( SMALLDATETIME,h.c.value('@effective-date[1]' , 'VARCHAR(20)')) 

	FROM ( SELECT XMLTEXT AS Xmlreport FROM #WORKINGXML) d
	CROSS APPLY Xmlreport.nodes('/BACSDocument/Data/MessagingAdvices') AS a(c)
	OUTER APPLY a.c.nodes('MessagingAdvice') h(c)
	) q([null],  [user-number], [effective-date])

group by [effective-date]

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-06-29 : 09:44:29

Cheers Guys, all sorted now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources