Post reply

Splitting XML

  • March 8, 2011 at 1:56 am

    #233022

    Hi guys,

    I have one XML file which has 3 smaller xml files and would like to split it into3 pieces so that the there is one 'root' element in every XML column of data type XML.

    Can anyone shed some light on ways to achieve this (XQuery?)?

    Cheers and Thanks!

    J.

    <root>

    <field1>Test 1</field1>

    <field2>Test 2</field2>

    <field3>Test 3</field3>

    </root>

    <root>

    <field1>Test 4</field1>

    <field2>Test 5</field2>

    <field3>Test 6</field3>

    </root>

    <root>

    <field1>Test 7</field1>

    <field2>Test 8</field2>

    <field3>Test 9</field3>

    </root>

  • March 8, 2011 at 7:59 am

    #1295892

    Is this what you need? Try this out. Let me know if it does not work.

    declare @var xml;

    set @var =

    '<DaddyRoot>

    <root>

    <field1>Test 1</field1>

    <field2>Test 2</field2>

    <field3>Test 3</field3>

    </root>

    <root>

    <field1>Test 4</field1>

    <field2>Test 5</field2>

    <field3>Test 6</field3>

    </root>

    <root>

    <field1>Test 7</field1>

    <field2>Test 8</field2>

    <field3>Test 9</field3>

    </root>

    </DaddyRoot>'

    select

    @var.query('//DaddyRoot/root[1]'),

    @var.query('//DaddyRoot/root[2]'),

    @var.query('//DaddyRoot/root[3]')

    - arjun

    https://sqlroadie.com/

  • March 8, 2011 at 8:08 am

    #1295899

    Thanks Arjun for your solution, however, what if the 'large' xml file contains 100,000 'root' nodes?

    In addition, the solution returns three columns, not three rows.

    Do you think it is possible to modify your query and make it fit for the above?

    Thanks

    Regards,

    J

  • March 8, 2011 at 11:43 pm

    #1296326

    Jonathan Mallia (3/8/2011)

    Thanks Arjun for your solution, however, what if the 'large' xml file contains 100,000 'root' nodes?

    In addition, the solution returns three columns, not three rows.

    Do you think it is possible to modify your query and make it fit for the above?

    Yes, this is possible.

    Jonathan Mallia (3/8/2011)

    Hi guys,

    I have one XML file which has 3 smaller xml files and would like to split it into3 pieces so that the there is one 'root' element in every XML column of data type XML.

    You never mentioned you want them in rows Jonathan. Let me try it out. I believe there is a better solution, one which is scalable as well.

    - arjun

    https://sqlroadie.com/

  • March 9, 2011 at 12:17 am

    #1296331

    Jonathan, check this out. I believe this is an optimal solution. Let me know if it works for you.

    declare @var xml;

    set @var =

    '<DaddyRoot>

    <root>

    <field1>Test 1</field1>

    <field2>Test 2</field2>

    <field3>Test 3</field3>

    </root>

    <root>

    <field1>Test 4</field1>

    <field2>Test 5</field2>

    <field3>Test 6</field3>

    </root>

    <root>

    <field1>Test 7</field1>

    <field2>Test 8</field2>

    <field3>Test 9</field3>

    </root>

    </DaddyRoot>'

    select

    c.query('.')

    from

    @var.nodes('//root') as tab(c)

    - arjun

    https://sqlroadie.com/

  • March 9, 2011 at 12:36 am

    #1296338

    It is more appropriate to use

    select

    c.query('.')

    from

    @var.nodes('//DaddyRoot/root') as tab(c)

    as this looks precisely for 'root' child nodes of 'DaddyRoot'.

    Reading this will help you - http://msdn.microsoft.com/en-us/library/ms188282.aspx

    - arjun

    https://sqlroadie.com/

  • March 9, 2011 at 5:37 am

    #1296424

    Thanks Arjun for your help. Solution perfectly works 😉

  • March 9, 2011 at 5:50 am

    #1296431

    Cool 🙂

    -arjun

    https://sqlroadie.com/

Viewing 8 posts - 1 through 8 (of 8 total)

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