Question

ddomanil on Wed, 16 Apr 2014 16:34:50


please consider the xml

<midata>
	<person>
		<id1>c</id1>
		<id2>1</id2>
		<details>
			<firstname>john</firstname>
			<lastname>smith</lastname>
		</details>
	</person>
	<address>
		<id1>c</id1>
		<id2>1</id2>
		<details>
			<line1>50 hollywood lane</line1>
			<line2>hollywood</line2>
		</details>
	</address>
	<address>
		<id1>c</id1>
		<id2>1</id2>
		<details>
			<line1>6 sydney crescent</line1>
			<line2>newcastle</line2>
		</details>
	</address>
	<address>
		<id1>t</id1>
		<id2>6</id2>
		<details>
			<line1>66 new york st</line1>
			<line2>New york</line2>
		</details>
	</address>
</midata>

can someone help me with the tsql xquery to output a table which looks like this

firstname	lastname	addressline1		addressline2
john		smith		50 hollywood lane	hollywood
john		smith		6 sydney crescent	newcastle


Sponsored



Replies

Erland Sommarskog on Wed, 16 Apr 2014 21:43:21


declare @x xml = '<midata>
   <person>
      <id1>c</id1>
      <id2>1</id2>
      <details>
         <firstname>john</firstname>
         <lastname>smith</lastname>
      </details>
   </person>
   <address>
      <id1>c</id1>
      <id2>1</id2>
      <details>
         <line1>50 hollywood lane</line1>
         <line2>hollywood</line2>
      </details>
   </address>
   <address>
      <id1>c</id1>
      <id2>1</id2>
      <details>
         <line1>6 sydney crescent</line1>
         <line2>newcastle</line2>
      </details>
   </address>
   <address>
      <id1>t</id1>
      <id2>6</id2>
      <details>
         <line1>66 new york st</line1>
         <line2>New york</line2>
      </details>
   </address>
</midata>'

SELECT P.c.value('(person/details/firstname/text())[1]', 'varchar(30)') AS Firstname,
       P.c.value('(person/details/lastname/text())[1]', 'varchar(30)') AS Lastname,
       A.c.value('(details/line1/text())[1]', 'varchar(50)') AS AdressLine1,
       A.c.value('(details/line2/text())[1]', 'varchar(50)') AS AdressLine2
FROM   @x.nodes('/midata') AS P(c)
CROSS  APPLY P.c.nodes('address') AS A(c)

wBob on Thu, 17 Apr 2014 14:21:45


I think the OP wants to "join" the person elements to the address elements based on id1 and id2.  There are a few different approaches to this.  I tried a location path, but this wont work if there is more than one person, eg something like:

SELECT
	'location path' s,
	p.c.value('(details/firstname/text())[1]', 'VARCHAR(30)') firstname,
	p.c.value('(details/lastname/text())[1]', 'VARCHAR(30)') lastname,
	a.c.value('(details/line1/text())[1]', 'VARCHAR(30)') line1,
	a.c.value('(details/line2/text())[1]', 'VARCHAR(30)') line2
FROM @xml.nodes('midata') m(c)
	CROSS APPLY m.c.nodes('person') p(c)
	CROSS APPLY m.c.nodes('address[id1=../person/id1][id2 = ../person/id2]') a(c)

It may be there's a better way of doing that.

The other approaches are to materialise the elements as resultsets and join them ( eg in CTEs or using temp tables ), or to restructure the XML.  Please see below for examples of each:

DECLARE @xml XML = '<midata>
	<person>
		<id1>c</id1>
		<id2>1</id2>
		<details>
			<firstname>john</firstname>
			<lastname>smith</lastname>
		</details>
	</person>

	<person>
		<id1>w</id1>
		<id2>99</id2>
		<details>
			<firstname>w</firstname>
			<lastname>Bob</lastname>
		</details>
	</person>

	<address>
		<id1>c</id1>
		<id2>1</id2>
		<details>
			<line1>50 hollywood lane</line1>
			<line2>hollywood</line2>
		</details>
	</address>
	<address>
		<id1>c</id1>
		<id2>1</id2>
		<details>
			<line1>6 sydney crescent</line1>
			<line2>newcastle</line2>
		</details>
	</address>
	<address>
		<id1>t</id1>
		<id2>6</id2>
		<details>
			<line1>66 new york st</line1>
			<line2>New york</line2>
		</details>
	</address>

	<address>
		<id1>w</id1>
		<id2>99</id2>
		<details>
			<line1>Bob Avenue</line1>
			<line2>Bobsville</line2>
		</details>
	</address>

</midata>'


-- Option 1: Materialise all the resultsets and join them; as database people we like this approach
-- although may not scale over large piece of XML; if not use intermediate temp tables instead.
;WITH persons AS
	(
	SELECT
		p.c.value('(id1/text())[1]', 'VARCHAR(5)') id1,
		p.c.value('(id2/text())[1]', 'VARCHAR(5)') id2,
		p.c.value('(details/firstname/text())[1]', 'VARCHAR(30)') firstname,
		p.c.value('(details/lastname/text())[1]', 'VARCHAR(30)') lastname
	FROM @xml.nodes('midata/person') p(c)
	), addresses AS
	(
	SELECT
		p.c.value('(id1/text())[1]', 'VARCHAR(5)') id1,
		p.c.value('(id2/text())[1]', 'VARCHAR(5)') id2,
		p.c.value('(details/line1/text())[1]', 'VARCHAR(30)') line1,
		p.c.value('(details/line2/text())[1]', 'VARCHAR(30)') line2
	FROM @xml.nodes('midata/address') p(c)
	)
SELECT 'materialise' s, p.id1, p.id2, p.firstname, p.lastname, a.line1, a.line2
FROM persons p
	INNER JOIN addresses a ON p.id1 = a.id1
		AND p.id2 = a.id2


-- -- Option 2: Restrucure the xml; can make it attribute or element-based, or both
SELECT @xml.query('
<root>
{
for $p in midata/person
return
	(
	for $a in midata/address[id1 = $p/id1][id2 = $p/id2]/details
	return 
	<person id1="{$p/id1}" id2="{$p/id2}" firstname="{$p/details/firstname[1]}" lastname="{$p/details/lastname[1]}">
		<address>
			{$a/line1}
			{$a/line2}
		</address>
	</person>
	)
}
</root>
') p


-- This new piece of xml is now much easier to query;
-- very XQuery focused solution; again unlikely to scale over large piece of XML
SELECT 
	'restructure' s,
	p.c.value('@id1', 'VARCHAR(30)') id1,
	p.c.value('@id2', 'VARCHAR(30)') id2,
	p.c.value('@firstname', 'VARCHAR(30)') firstname,
	p.c.value('@lastname', 'VARCHAR(30)') lastname,
	a.c.value('(line1/text())[1]', 'VARCHAR(30)') line1,
	a.c.value('(line2/text())[1]', 'VARCHAR(30)') line2

FROM
	(	-- This is the same query as above, done as a subquery
	SELECT @xml.query('
	<root>
	{
	for $p in midata/person
	return
		(
		for $a in midata/address[id1 = $p/id1][id2 = $p/id2]/details
		return 
		<person id1="{$p/id1}" id2="{$p/id2}" firstname="{$p/details/firstname[1]}" lastname="{$p/details/lastname[1]}">
			<address>
				{$a/line1}
				{$a/line2}
			</address>
		</person>
		)
	}
	</root>
	')
	) x(y)
	CROSS APPLY x.y.nodes('root/person') p(c)
		CROSS APPLY p.c.nodes('address') a(c)

HTH

Visakh16 on Thu, 17 Apr 2014 15:13:26


SELECT firstname,lastname,m.n.value('(./details/line1)[1]') as line1,m.n.value('(./details/line2)[1]') as line2
FROM (SELECT @X AS xmlval,p.q.value('./id1[1]','char(5)') as personid1,p.q.value('./id2[1]','int') as personid2,p.q.value('./firstname[1]','varchar(100)') as firstname,p.q.value('./lastname[1]','varchar(100)') as lastname
FROM @X.nodes('/midata/person')p(q)
) t
CROSS APPLY xmlval.nodes('/midata/address[./id1=sql:column("personid1") And ./id2 = SQL:column("personid2")]')m(n)

This?