Reading XML - 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 2008 Forums

Transact-SQL (2008)

Reading XML

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-12 : 12:02:12

I have a bunch of 3rd party XML files, which I need to make availible to our clients vis reporting services. Each type of XML file will have a different report template. I am looking for an easy way to find out what report template i need for the xml file as the xml files do not have the same structure ie.

Type 1

<Data>
<MessagingAdvice>
<MessagingHeader report-type="1234"

Type 2
<Data>
<input Report>
<Header report-type="5423"

I am looking to find an easy way to do something like
Case @report-type
When "1234" then 1
when "5423" then 2
etc

is this possible ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-12 : 12:33:45

If they all have the "report-type" attribute somewhere, you can query for it like this:

@YourXMLData.value('(//*/@report-type)[1]','varchar(32)')

If you suspect there may be multiple report types, then use query function rather than value function to pick up all of them.

@YourXMLData.query('data(//*/@report-type)')

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-12 : 12:59:29

Thank yo for your help so far, Im am struggling a bit.

if i have the following code, how can I apply your suggestion ?

SET @sql = '(SELECT * FROM OPENROWSET(BULK ''' + @FileNAME + ''' , SINGLEBLOB) AS x)'
SET @X = @sql

WITH NameSpace ('http://www.w3.org/2001/XMLSchema-instance' As xsi)

/*
Select case ????
When '1234' then 1
When '5423' then 2
Else 0 End
*/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-12 : 14:31:00

You would need to do the query and run the xquery statement against the results of the query like this:

SELECT 
	CASE c.value('(//*/@report-type)[1]','varchar(32)')
		WHEN '1234' THEN 1
		WHEN '5323' THEN 2
		ELSE 0
    END
FROM 
(
	SELECT CAST(x.c AS XML) FROM OPENROWSET(BULK 'C:\Filename.txt',SINGLE_BLOB) AS X(c)
) S(c)

If you want to use dynamic SQL use sp_executesql with parameters like this:

DECLARE @FileNAME VARCHAR(255); 
DECLARE @sql NVARCHAR(4000),
        @parms NVARCHAR(500),
        @xmlvar XML;
        
SET @FileName = 'C:\Filename.txt';
SET @sql =  '(SELECT @xmlvar = x.c FROM OPENROWSET(BULK ''' + @FileNAME + ''' , SINGLE_BLOB) AS x(c))'
SET @parms = '@xmlvar XML OUTPUT';

EXEC sp_executesql @sql, @parms, @xmlvar OUTPUT;

SELECT 
	CASE @xmlvar.value('(//*/@report-type)[1]','varchar(32)')
		WHEN '1234' THEN 1
		WHEN '5323' THEN 2
		ELSE 0
    END

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-14 : 05:36:45

Right nearly sorted it. just one more question.

if i use the following where there are two records in the xml file
SELECT
@xmlvar.value('(//*/@user-number)[1]','varchar(32)') As 'User ID',
@xmlvar.query('data(//*/@payer-name)') as 'Payer',
@xmlvar.value('(//*/@report-type)[1]','varchar(32)') AS 'ReportType'

why am I only getting one record back with both @payer-name in one field. Hoe can i get a set back with the two rows and only one payer name in each row as you would do in a normal select function

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-14 : 07:16:22

Depends on the structure of your XML data. I would be glad to suggest something more precisely if you can post a sample of the XML that you are using. Here are a few examples with a sample I constructed - I am using query function or value function really at random because in this example, you can use either.

DECLARE @x XML;
SET @x = '
<Elem1>
  <a>xx</a>
  <b>yy</b>
</Elem1>
<Elem1>
  <a>12</a>
  <b>34</b>
</Elem1>
';

-- All values of b in one xstring
SELECT 
	@x.value('(//a)[1]','varchar(32)'),
	@x.query('data(//b)');

-- one row for each Elem1, but second value of node a	
SELECT
	c.value('(//a)[2]','varchar(32)'),
	c.query('data(b)')
FROM
	@x.nodes('/Elem1') T(c)

-- one row for each Elem1 with corresponding values of a and b
SELECT
	c.value('a[1]','varchar(32)'),
	c.query('data(b)')
FROM
	@x.nodes('/Elem1') T(c)

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-14 : 09:40:10

Hi

Thanks for you help so far.

Xml

<?xml version="1.0" encoding="UTF-8"?>

<BACSDocument>
<Data>
<MessagingAdvices>
<MessagingHeader document-number="0" advice-type="ADDACS" subject-first-aosn="00002735" subject-last-aosn="00002736" user-number="000000" stream-identifier="0" envelope-sequence-number="00001499" report-generation-date="2012-03-12" user-name="TEST COMPANY" report-type="7001"></MessagingHeader>
<AddresseeInformation name=" "></AddresseeInformation>
<MessagingAdvice user-number="000000" record-type="A" effective-date="2012-03-12" reference="REFERENCE1" payer-name="JOHN SMITH" payer-account-number="12345678" payer-sort-code="123456" reason-code="1" aosn="00002735"></MessagingAdvice>
<MessagingAdvice user-number="000000" record-type="A" effective-date="2012-03-12" reference="REFERENCE1" payer-name="MR SMITH" payer-account-number="87654321" payer-sort-code="654321" reason-code="1" aosn="00002736"></MessagingAdvice>
<MessagingError></MessagingError>
</MessagingAdvices>
</Data>
</BACSDocument>

My code

DECLARE @parms NVARCHAR(500), @sql nvarchar(4000),
@xmlvar XML;

DECLARE @FullFile VARCHAR(150) = 'C:\XMLReports\TEST\TEST_ADDACS_ADVICE_REPORT.XML'
SET @sql = '(SELECT @xmlvar = x.c FROM OPENROWSET(BULK ''' + @FullFile + ''' , SINGLE_BLOB) AS x(c))'
SET @parms = '@xmlvar XML OUTPUT';

EXEC sp_executesql @sql, @parms, @xmlvar OUTPUT;

SELECT
@xmlvar.value('(//*/@user-number)[1]','varchar(32)') As 'User ID',
@xmlvar.query('data(//*/@payer-name)') as 'Payer',
@xmlvar.value('(//*/@report-type)[1]','varchar(32)') AS 'ReportType'
FROM @xmlvar.nodes('//MessagingAdvice') T(xmlvar)

Result

User ID Payer ReportType
000000 JOHN SMITH MR SMITH 7001
000000 JOHN SMITH MR SMITH 7001

required output

User ID Payer ReportType
000000 JOHN SMITH 7001
000000 MR SMITH 7001

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-14 : 10:36:14

Change the SELECT query like this:

SELECT
	x.query('data(./@user-number)') As 'User ID',
	x.query('data(./@payer-name)') as 'Payer',
	x.value('(//*/@report-type)[1]','varchar(32)') AS 'ReportType'
FROM @xmlvar.nodes('//MessagingAdvice') T(x)

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-14 : 12:24:01

Brillaint!

Thank you, this has given me a great insight on the subject, which should enable me to tackle more complex xml reports

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources