August 13, 2015 at 11:09 am
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>'
SELECTCONVERT(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)
SELECTCONVERT(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)
August 13, 2015 at 12:43 pm
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>'
SELECTx.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)
January 14, 2016 at 1:58 am
I also faced similar difficulty when I was trying. I think programming difficult similar to writing essay. But everything is possible I believe.
February 27, 2020 at 6:24 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply