How do I loop through some XML data in SQL Server 2008?

Question

I've been struggling with this for a good 2.5 hours and could really use some help. There can be any number of ATTACHMENTS included and I'm trying to pull out the FILENAME and DESCRIPTION from each. Here's my code (excuse the mess) I would greatly appreciate any help you can give. Thanks!

DECLARE
   @myXML XML = NULL
  ,@hDoc  INT = 0
  ,@Num   VARCHAR(2)
  ,@Type  VARCHAR(5)
  ,@FileName    VARCHAR(32) = ''
  ,@Description VARCHAR(256) = '';

SET @myXML = '
<REPORT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" DESCRIPTION="TestOrder" FILENUM="1234">
    <ADDENDA>
        <ATTACHMENTS>
            <ATTACHMENT NUM="1" TYPE="1">
                <FILENAME>4215.pdf</FILENAME>
                <DESCRIPTION>Sales Contract</DESCRIPTION>
                <CONTENT>...</CONTENT>
            </ATTACHMENT>
            <ATTACHMENT NUM="2" TYPE="1">
                <FILENAME>6719.pdf</FILENAME>
                <DESCRIPTION>Contract Instruction</DESCRIPTION>
                <CONTENT>...</CONTENT>
            </ATTACHMENT>
        </ATTACHMENTS>
    </ADDENDA>
</REPORT>';

----------------------------------------------------------------------------
EXEC sp_xml_preparedocument @hDoc OUTPUT, @myXML;
----------------------------------------------------------------------------
-- Begin get required docs 
DECLARE xDocsCursor CURSOR FOR
  SELECT    *
  FROM OPENXML (@hDoc, '/REPORT/ADDENDA/ATTACHMENTS/ATTACHMENT',1)
          WITH ([NUM]  varchar(32),
                [TYPE] varchar(5))
          WHERE [NUM] IS NOT NULL;
OPEN xDocsCursor;

FETCH NEXT FROM xDocsCursor INTO @Num, @Type;
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @Num, @Type;
  -------------------
  FETCH NEXT FROM xDocsCursor INTO @Num, @Type;
END
CLOSE xDocsCursor;
DEALLOCATE xDocsCursor;
-- End get required docs /REPORT/TRACKING/REQUIREDDATA
----------------------------------------------------------------------------
----------------------------------------------------------------------------
EXEC sp_xml_removedocument @hDoc;
----------------------------------------------------------------------------

Why don't you switch to newer cleaner native XQuery instead of old-fashioned OPENXML()? — har07
– har07, Commented Jun 17, 2015 at 0:32

marc_s · Accepted Answer · 2015-06-17 04:58:34Z

1

I would use the native XQuery support in SQL Server to do this - much simpler, less messy than the legagy OpenXML approach:

SELECT
    Num = XAttachments.value('@NUM', 'int'),
    [Type] = XAttachments.value('@TYPE', 'int'),
    FileName = XAttachments.value('(FILENAME)[1]', 'varchar(100)'),
    Description = XAttachments.value('(DESCRIPTION)[1]', 'varchar(100)')
FROM 
    @myxml.nodes('/REPORT/ADDENDA/ATTACHMENTS/ATTACHMENT') AS XT(XAttachments)

Produces output of:

enter image description here

answered Jun 17, 2015 at 4:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bert Gregor Over a year ago

Yes, this did exactly what I wanted it to do with a minimal amount of coding. Thank you very much for taking your valuable time to help me out!

Dan Field Over a year ago

@marc_s, could I ask how your answer significantly differs from mine?

Dan Field · Accepted Answer · 2015-06-17 01:34:44Z

0

You just need to add this to your OPENXML:

SELECT    *
FROM OPENXML (@hDoc, '/REPORT/ADDENDA/ATTACHMENTS/ATTACHMENT',1)
      WITH ([NUM]  varchar(32),
            [TYPE] varchar(5),              
            [FILENAME] VARCHAR(25) './FILENAME',
            DESCRIPTION VARCHAR(50) './DESCRIPTION',
            CONTENT VARCHAR(50) './CONTENT')
      WHERE [NUM] IS NOT NULL;

I'm not quite sure why you want to use a CURSOR here, as you can get the rows returned this way in a single table for manipulation/query. If you prefer the 'modern' XQuery format, here it is (this would not require running sp_xml_preparedocument, and should perform better).

SELECT x.value('@NUM', 'VARCHAR(32)') as NUM
      ,x.value('@TYPE', 'VARCHAR(5)') as TYPE
      ,x.value('./FILENAME[1]', 'VARCHAR(30)') AS FILENAME
      ,x.value('./DESCRIPTION[1]', 'VARCHAR(30)') AS DESCRIPTION
      ,x.value('./CONTENT[1]', 'VARCHAR(30)') AS CONTENT
FROM @myXML.nodes('/REPORT/ADDENDA/ATTACHMENTS/ATTACHMENT') T(x)

edited Jun 17, 2015 at 1:34

answered Jun 17, 2015 at 1:28

Dan Field

21.8k6 gold badges60 silver badges74 bronze badges

1 Comment

Bert Gregor Over a year ago

I was using a CURSOR because I was so frustrated I was trying anything. Thanks for your help. Your answer of course worked. Again, I really appreciate it!

Collectives™ on Stack Overflow

How do I loop through some XML data in SQL Server 2008?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related