XML nodes extraction

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » XML nodes extraction

XML nodes extraction

Rate Topic

Display Mode

Topic Options

Author

Message

oliver.morris

Posted Friday, March 08, 2013 5:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 22, 2013 8:51 AM
Points: 123, Visits: 228

Hi,

Thanks for your help in advance, working with XML nodes for the first time and finding it frustrating. I am trying to build a relational table from some xml. setting each xml level to a new column in a table.

CREATE TABLE ClientInfo
(
  ID INT PRIMARY KEY IDENTITY,
  XMLInfo XML
);

INSERT INTO ClientInfo (XMLInfo)
VALUES
(
N'<?xml version="1.0" ?>
<Root>
      <key>29</key>
      <children>
			<key>500</key>
			<children>
				<key>3000</key>
				<children>
					<key>70000</key>
				</children>
			</children>
	  </children>
	  <key>50</key>
      <children>
			<key>600</key>
			<children>
				<key>3600</key>
				<children>
					<key>9862</key>
				</children>
				<children>
					<key>9863</key>
				</children>
				<children>
					<key>9864</key>
				</children>
			</children>
	  </children>
	  <key>70</key>
      <children>
			<key>900</key>
			<children>
				<key>3700</key>
				<children>
					<key>7862</key>
				</children>
				<children>
					<key>6863</key>
				</children>
				<children>
					<key>5864</key>
				</children>
			</children>
			<children>
				<key>5400</key>
				<children>
					<key>7892</key>
				</children>
				<children>
					<key>6563</key>
				</children>
				<children>
					<key>5464</key>
				</children>
			</children>
	  </children>
</Root>
');

select * from [dbo].[ClientInfo]

SELECT
		aref.value('(key/text())[1]', 'varchar(50)') as Column1,
		bref.value('(key/text())[1]', 'varchar(50)') as Column2,
		cref.value('(key/text())[1]', 'varchar(50)') as Column3,
		dref.value('(key/text())[1]', 'varchar(50)') as Column4
FROM  
      ClientInfo CROSS APPLY
      XmlInfo.nodes('//Root') AS Level1(aref) CROSS APPLY
	  aref.nodes('children') AS Level2(bref)  CROSS APPLY
	  bref.nodes('children') AS Level3(cref) CROSS APPLY
	  cref.nodes('children') AS Level4(dref)
GO

When I run this query column 1 just has the first ID in this case 29 ? I would very much like it to report the other key's adjacent to their relevant children keys. Why is this not working for me.

Any useful material you can forward on would be gratefully appreciated.

Many Thanks,

Oliver

Post #1428521

Vedran Kesegic

Posted Saturday, March 09, 2013 6:54 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 343, Visits: 1,077

Your xml format is not very well organized. Keys and children are not in any relation except positional, so it is almost impossible to relate them.
You should make children of the key to appear WITHING the parent key, not side by side.
Closest I could get is:

declare @ClientInfo xml =
N'<?xml version="1.0" ?>
<Root>
      <key>29</key>
      <children>
			<key>500</key>
			<children>
				<key>3000</key>
				<children>
					<key>70000</key>
				</children>
			</children>
	  </children>
	  <key>50</key>
      <children>
			<key>600</key>
			<children>
				<key>3600</key>
				<children>
					<key>9862</key>
				</children>
				<children>
					<key>9863</key>
				</children>
				<children>
					<key>9864</key>
				</children>
			</children>
	  </children>
	  <key>70</key>
      <children>
			<key>900</key>
			<children>
				<key>3700</key>
				<children>
					<key>7862</key>
				</children>
				<children>
					<key>6863</key>
				</children>
				<children>
					<key>5864</key>
				</children>
			</children>
			<children>
				<key>5400</key>
				<children>
					<key>7892</key>
				</children>
				<children>
					<key>6563</key>
				</children>
				<children>
					<key>5464</key>
				</children>
			</children>
	  </children>
</Root>
'


SELECT
	ParentID = c.x.value('../key[1]', 'int'),
	ChildID = c.x.value('key[1]', 'int')
FROM  @ClientInfo.nodes('//children') c(x) -- "//children" means "children nodes at all levels"
GO

The result is:

ParentID    ChildID
----------- -----------
29          500
500         3000
3000        70000
29          600
600         3600
3600        9862
3600        9863
3600        9864
29          900
900         3700
3700        7862
3700        6863
3700        5864
900         5400
5400        7892
5400        6563
5400        5464

(17 row(s) affected)

If you change xml structure as suggested, similar construction would work perfectly.

Since preceding-sibling is not supported by sql2008, you could look into >> and << xml operators that I'm not very familiar with.
Some examples are here:
http://stackoverflow.com/questions/3238844/getting-the-following-sibling-in-an-xpath-when-following-axis-is-not-supported
and here
http://dbaspot.com/sqlserver-programming/212706-how-move-xml-nodes-via-xquery-sql-server-2005-a-2.html

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1428946

oliver.morris

Posted Monday, March 11, 2013 2:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 22, 2013 8:51 AM
Points: 123, Visits: 228

Many Thanks for your help, sorry about the poorly constructed xml. I will have a look at the links you passed on.

Thanks again,

Oliver

Post #1429110

« Prev Topic | Next Topic »

Permissions