From XML to Rowset (Tables) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

From XML to Rowset (Tables)

Author

Topic

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-26 : 09:05:25

Hello, i need som help, baaad.

I have a hierarcy XML file that contains about 50 trees and subtrees

. I would like to transform this and return it into 1 single rowset table. Tricky stuff aint it, maybe OPENXML can fix this?, but i know you guys got the skills to hellp me

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-26 : 11:32:48

so you wan't to build a hierarchy table from xml???

Go with the flow & have fun! Else fight the flow

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-26 : 12:09:06

jepp, there the columns and rows represents the nodes and hierarcy

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-26 : 12:25:48

so have you tried openxml?
sample data (insert into statements) and create table statements would be usefull...

Go with the flow & have fun! Else fight the flow

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-26 : 12:48:07

Yee.
Im using it right now, this is what i have come up with:


DECLARE @idoc int 
EXEC sp_xml_preparedocument @idoc OUTPUT,
'
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Root>
	<Question0>
		<Question>How do you do?</Question>
		<Answer0>
			<Answer>Im good thanks</Answer>
			<Question1>
				<Question>Why are you feeling fine boy?</Question>
				<Answer1>
					<Answer>None of your business, b***h</Answer>
				</Answer1>
			</Question1>
		</Answer0>
		<Answer0>
			<Answer>I aint fine, nooo</Answer>
			<Question1>
				<Question>Something i can help you with?</Question>
				<Answer1>
					<Answer>No, i dont think so, thanks anyway dude</Answer>
				</Answer1>
			</Question1>
		</Answer0>
		<Answer0>
			<Answer>Could have been better dude</Answer>
			<Question1>
				<Question>How come?</Question>
				<Answer1>
					<Answer>Dont know</Answer>
				</Answer1>
			</Question1>
		</Answer0>
	</Question0>
</Root>
'
--DROP TABLE #XMLTable 
SELECT * into #XMLTable FROM OPENXML (@idoc, '/Root/*', 0)
EXEC sp_xml_removedocument @idoc  
SELECT * FROM #XMLTable

Try this, you can just Copy-n-Paste right away and see if you could make a hierarchy-table of it?. But please do remember that this XML document is just 1/100 of the Real XML document, so dont hardcode any columns or anything like that, it has to work for EVERY XML document that is loaded in.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-26 : 12:57:27

so what's wrong with the table you get?
id and parentid are hierarchyly fine to me.
am i missing something?

Go with the flow & have fun! Else fight the flow

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-26 : 13:27:10

I want the columns/rows to expand horizantaly so you get the impression that it is hierarchy even though it just 1 table, like this:
Columns: Question0,Answer0,Question1,Answer1,Question2,Answer2

I want it to look like this:

 
  Q0:How do you do?
     A0:Im good thanks
        Q1:Why are you feeling fine boy?
            A1:None of your business, b***h
     A0:I aint fine, nooo
        Q1:Something i can help you with?
            A1:No, i dont think so, thanks anyway dude
     A0:Could have been better dude
        Q1:How come?
            A1:Dont know

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-26 : 13:46:52

that's a formatting issue. do this at the presentation layer.
you can use a tree control...
or you can use spaces for prefixes.

Go with the flow & have fun! Else fight the flow

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-26 : 13:51:18

I dont want a treecontrol, i want a table, just an ordinary Rowset(table) presentation of hierarchy data. But please if you have any ideas on how to make a DataTable of this in .NET, please do post some good code that shows how its done.

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-26 : 14:27:44

im about to do some cool serious sh*t right now , im on the right track i think

take a look at this sweet stuff.. :



DECLARE @idoc int 
EXEC sp_xml_preparedocument @idoc OUTPUT,
'
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Root>
	<Question0>
		<Question>How do you do?</Question>
		<Answer0>
			<Answer>Im good thanks</Answer>
			<Question1>
				<Question>Why are you feeling fine boy?</Question>
				<Answer1>
					<Answer>None of your business, b***h</Answer>
				</Answer1>
			</Question1>
		</Answer0>
		<Answer0>
			<Answer>I aint fine, nooo</Answer>
			<Question1>
				<Question>Something i can help you with?</Question>
				<Answer1>
					<Answer>No, i dont think so, thanks anyway dude</Answer>
				</Answer1>
			</Question1>
		</Answer0>
		<Answer0>
			<Answer>Could have been better dude</Answer>
			<Question1>
				<Question>How come?</Question>
				<Answer1>
					<Answer>Dont know</Answer>
				</Answer1>
			</Question1>
		</Answer0>
	</Question0>
</Root>
'
--DROP TABLE #XMLTable 
SELECT * into #XMLTable FROM OPENXML (@idoc, '/Root/*', 0)
EXEC sp_xml_removedocument @idoc  

DECLARE @caseSQL varchar(8000)
SET @caseSQL=''
DECLARE @minID int
DECLARE @maxID int
SELECT @minID=MIN(id),@maxID=MAX(id) FROM #XMLTable WHERE nodetype=3
WHILE(@minID<@maxID+1)
BEGIN
SET @caseSQL=@caseSQL + ',CASE WHEN id=' + CONVERT(varchar(20),@minID) + ' THEN text END'
SET @minID=@minID+1
END
EXEC('SELECT id' + @caseSQL + ' FROM #XMLTable WHERE nodetype=3')

It aint perfect yet as you can see, i want it to be more compact like i showed you above.. you can just copy-n-paste it..

gpro102
Starting Member

2 Posts

Posted - 2005-06-03 : 19:02:04

This is similar to yours. I have the following xml that I need to retrieve the value bases on the condition

-------------------- -------
NodeA/NodeB BB
NodeA/NodeC/NodeE CC
NodeA/NodeC/NodeD ?
...
or
I need to a list like this

NodeB NodeE NodeD
---------- ---------- -----------
BB CC 10
BB DD 20
EE CC 30
FF CC 40
FF DD 50

I know I can accomplish this by hard coding the openxml

select * from OPENXML(@idoc, 'ACORD/NodeA/NodeC',3)
with (NodeB varchar(10) '../NodeB',
NodeE varchar(10),
NodeD int)

But I can't. How can I get the same result by query the #temp table?

------------------------------------------
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUT,
'
<ACORD>
<NodeA>
<NodeB>BB</NodeB>
<NodeC>
<NodeD>10</NodeD>
<NodeE>CC</NodeE>
</NodeC>
<NodeC>
<NodeD>20</NodeD>
<NodeE>DD</NodeE>
</NodeC>
</NodeA>
<NodeA>
<NodeB>EE</NodeB>
<NodeC>
<NodeD>30</NodeD>
<NodeE>CC</NodeE>
</NodeC>
</NodeA>
<NodeA>
<NodeB>FF</NodeB>
<NodeC>
<NodeD>40</NodeD>
<NodeE>CC</NodeE>
</NodeC>
<NodeC>
<NodeD>50</NodeD>
<NodeE>DD</NodeE>
</NodeC>
</NodeA>
</ACORD>
'

SELECT id,parentid,nodetype,left(localname,10) as localname,prev,substring(text,1,10) as value
into #temp
FROM OPENXML(@idoc, 'ACORD')
EXEC sp_xml_removedocument @idoc
select * from #temp
---------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources