Difficulty in partially shredding xml in sql server 2005

Question

I'm using SQl Server 2005 and need to partially shred relatively large xml files in a stored procedure.  By partially shred, I mean that each xml file may contain 1000's of segments, and I need to "extract" all of any given segment, and hand it off to another tsql process for detailed processing.  The xml file is basically structured like:
    <start>
        <segment>
             <data>
              ...
             </data>
        </segment>
        <segment>
             <data>
              ...
             </data>
        </segment>
    </start>

What I am attempting to accomplish is to extract, in XML, all of the information from <segment> to </segment> so that it can be shipped off to another process.

I've attempted various mechanisms without success, and apparently I am missing something.  I've already got the counts and looping handled, but can't for some reason, pull out the correct data.

Any help would be appreciated.

Answer 1

You can store the shredded pieces of your XML File into a Temporary Table. 

declare @xmlfile xml

set @xmlfile = '<start>

        <segment>
             <data>
              ...
             </data>
        </segment>
        <segment>
             <data>
              ...
             </data>
        </segment>

    </start>'

Insert into #TmpTable (shreddedxmlfile)

SELECT T.c.query('.') AS result FROM @xmlfile.nodes('/start/segment') T(c) GO

Now pass each row in #TmpTable to your Process which will apply business logic on top of your shredded XML File.

Note: I would suggest this approach only if your XMLFile is not huge. 

If not, use any .Net code to read your Huge XML File and populate your Temp Table with the shredded XML Files.  And then process it! 

Regards

Shiv

Answer 2

Try this example:

DECLARE @xml XML

SET @xml = 
'<start>
  <segment>
    <data att1="1">test data 1</data>
  </segment>
  <segment>
    <data att2="2">test data 2</data>
  </segment>
</start>'
    
    
SELECT 
	x.y.query('.') AS segment,
	x.y.value('data[1]', 'VARCHAR(50)') AS your_data,
	x.y.value('(data/@att1)[1]', 'INT') AS att1,
	x.y.value('(data/@att2)[1]', 'INT') AS att2,
	x.y.value('(data/@att1, data/@att2)[1]', 'INT') AS att1
FROM @xml.nodes('//segment') x(y)

It's not entirely clear what your expected results are.  If the above example does not help, can you post some more detail?

Answer 3

I apologize for not being concise.  What I am attempting to do is taking a relatively large xml file ( generally around 2 to 4 Meg)and shredding it into individual segments or nodes, which I then intend on passing on as a message that is sent to a service broker.  I want to do the shredding so that I can take advantage of the parallelism capabilities of the ssb.

That being said, with the dummy xml that I provided initially:

<start>
        <segment>
             <data>
              ...
             </data>
        </segment>
        <segment>
             <data>
              ...
             </data>
        </segment>
    </start>

I need to extract each of the <segment> nodes and all data associated, individually so that each segment comprises an individual message to the service broker, where it will be treated as it's own self-contained xml message and processed.

So that in the end

Message 1: <start>
                       <segment>
                              <data>
                                    ...
                               </data>
                       </segment>
                  </start>

Message 2:<start>
                       <segment>
                              <data>
                                    ...
                               </data>
                       </segment>
                  </start>


Unfortunately, the source of the original xml file is a legacy system that I have no control over, and there is yet another system that processes the file before it's handed off to me. So having individual files created is not an option that is available to me.

I did take your sample and used it, and it did extract the <segment>, and when I used the [position()=sql:variable(:@i")], I was able to loop through the file and properly extract the data.

Thank you very much.