Unable to link two different areas of an XML when converting to Table

  • samalex

    SSCertifiable

    Points: 5360

    Hi all,

    I'm trying to parse an XML and get the data back to a table, and it's working though I'm unable to link pieces of data in the XML. Below is an example of what I have thus far. I can get the individual data to tables, but how can I tie the Tasks back to the People? There is no ID or anything in the XML that links the two.

    Thanks,

    Sam

    DECLARE @XML TABLE (XMLData XML);

    DECLARE @Person Table (Name NVARCHAR(50), Addresss NVARCHAR(50));

    DECLARE @Task Table (Name NVARCHAR(50), Details NVARCHAR(50));

    INSERT INTO @XML SELECT '

    <process>

    <header>

    <Person><Name>Test1</Name><Address>123 main street</Address></Person>

    <Tasks>

    <task><Name>Do some work</Name><details>details</details></task>

    <task><Name>Do some more work</Name><details>details more</details></task>

    </Tasks>

    </header>

    <header>

    <Person><Name>Test2</Name><Address>234 main street</Address></Person>

    <Tasks>

    <task><Name>Do some work 2</Name><details>details 2 </details></task>

    <task><Name>Do some more work3</Name><details>details more 3</details></task>

    </Tasks>

    </header>

    </process>'

    SELECT CONVERT(NVARCHAR(255),c.query('data(Name)')) AS name,

    CONVERT(NVARCHAR(255),c.query('data(Address)')) AS address

    FROM @XML r

    CROSS APPLY XMLData.nodes('process/header/Person') x(c)

    SELECT CONVERT(NVARCHAR(255),c.query('data(Name)')) AS name,

    CONVERT(NVARCHAR(255),c.query('data(details)')) AS address

    FROM @XML r

    CROSS APPLY XMLData.nodes('process/header/Tasks/task') x(c)

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    Hi, what you could do is to use a second cross apply within the same query to join on the other data. I've also tweaked the query to use the xml method "value" rather than doing a convert on the xml type which is what is returned when you use the "query" method:

    DECLARE @XML TABLE (XMLData XML);

    DECLARE @Person Table (Name NVARCHAR(50), Addresss NVARCHAR(50));

    DECLARE @Task Table (Name NVARCHAR(50), Details NVARCHAR(50));

    INSERT INTO @XML SELECT '

    <process>

    <header>

    <Person><Name>Test1</Name><Address>123 main street</Address></Person>

    <Tasks>

    <task><Name>Do some work</Name><details>details</details></task>

    <task><Name>Do some more work</Name><details>details more</details></task>

    </Tasks>

    </header>

    <header>

    <Person><Name>Test2</Name><Address>234 main street</Address></Person>

    <Tasks>

    <task><Name>Do some work 2</Name><details>details 2 </details></task>

    <task><Name>Do some more work3</Name><details>details more 3</details></task>

    </Tasks>

    </header>

    </process>'

    SELECT x.c.value('(Person/Name/text())[1]', 'nvarchar(255)') AS PersonName,

    x.c.value('(Person/Address/text())[1]', 'nvarchar(255)') AS PersonAddress,

    y.c.value('(Name/text())[1]', 'nvarchar(255)') as TaskName,

    y.c.value('(details/text())[1]', 'nvarchar(255)') as TaskName

    FROM @XML r

    CROSS APPLY XMLData.nodes('process/header') x(c)

    CROSS APPLY x.c.nodes('Tasks/task') y(c)

  • joseph_b.dickson

    SSC Rookie

    Points: 33

    I also faced similar difficulty when I was trying. I think programming difficult similar to writing essay. But everything is possible I believe.

  • This was removed by the editor as SPAM

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

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