SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 exstract part of xml field		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

whipit
Starting Member

4 Posts
Posted - 01/18/2013 :  11:07:49  Show Profile  Reply with Quote
Hi All
hope someone can help me

I have a field in a database called xml dump. example of contents below.
what i want to do is extract several parts of this field at different times. eg everyones name.
As you can see its not in a regular xml format. however i can use an xml converter online to create a normal xml doc however it would take some time to do 20,000 records.

how do i go about this many thanks in advance
Barry

<G id="561548022" dt="1347139797" stake="50/100" limit="3" game="1" type="3" seats="10" ver="1" pot="1800" rake="0" pc="9d,8c,2c,Qs,9s">
<title>
<![Deep Stack (6851218), No Limit Texas Holdem]]>
</title>
<PS self="3" dealer="5">
<P s="6" name="downts" c="Ks,8d" chips="15307.50" cs="102 M:102.1 B:153.1 #:18 VP:61 PFR:6 AF:7.5 W:17|50 STL:0|50 3B:0| CB:100| N:1400 STK:+1" hs="102 M:102.1 B:153.1 #:18 VP:61 PFR:6 AF:7.5 W:17|50 STL:0|50 3B:0| CB:100| N:1400 STK:+1"/>
<P s="1" name="mooby" c="," chips="3867.50" cs="101 M:25.8 B:38.7 #:18 VP:33 PFR:0 AF:0 W:29|50 STL:|0 3B:0| CB: N:425 STK:-1" hs="101 M:25.8 B:38.7 #:18 VP:33 PFR:0 AF:0 W:29|50 STL:|0 3B:0| CB: N:425 STK:-1"/>
<P s="3" name="kendo" c="Jh,Kc" chips="10860" cs="107 M:72.4 B:108.6 #:18 VP:22 PFR:0 AF:Inf. W:33|0 STL:0| 3B:0| CB:|100 N:-1350 STK:-8" hs="107 M:72.4 B:108.6 #:18 VP:22 PFR:0 AF:Inf. W:33|0 STL:0| 3B:0| CB:|100 N:-1350 STK:-8"/>
<P s="2" name="GANDHI" c="," chips="7060" cs="107 M:47.1 B:70.6 #:18 VP:17 PFR:6 AF:Inf. W:0| STL: 3B:0| CB:100| N:700 STK:-2" hs="107 M:47.1 B:70.6 #:18 VP:17 PFR:6 AF:Inf. W:0| STL: 3B:0| CB:100| N:700 STK:-2"/>
<P s="4" name="MAX" c="," chips="5602.50" cs="102 M:37.4 B:56 #:18 VP:33 PFR:28 AF:3.0 W:17|0 STL:50| 3B:0| CB:0| N:-1075 STK:+1" hs="102 M:37.4 B:56 #:18 VP:33 PFR:28 AF:3.0 W:17|0 STL:50| 3B:0| CB:0| N:-1075 STK:+1"/>
<P s="5" name="kazza" c="," chips="4782.50" cs="104 M:31.9 B:47.8 #:18 VP:28 PFR:0 AF:0 W:29|100 STL:0|50 3B:0| CB:|50 N:-100 STK:-2" hs="104 M:31.9 B:47.8 #:18 VP:28 PFR:0 AF:0 W:29|100 STL:0|50 3B:0| CB:|50 N:-100 STK:-2"/>
</PS>
<AS>
<A seq="1" type="1" s="6" v="50"/>
<A seq="2" type="2" s="1" v="100"/>
<A seq="3" type="27" s="3"/>
<A seq="4" type="3" s="2"/>
<A seq="5" type="4" s="3" v="100"/>
<A seq="6" type="3" s="4"/>
<A seq="7" type="4" s="5" v="100"/>
<A seq="8" type="4" s="6" v="50"/>
<A seq="9" type="6" s="1"/>
<A seq="10" type="28"/>
<A seq="11" type="6" s="6"/>
<A seq="12" type="6" s="1"/>
<A seq="13" type="7" s="3" v="200"/>
<A seq="14" type="3" s="5"/>
<A seq="15" type="4" s="6" v="200"/>
<A seq="16" type="3" s="1"/>
<A seq="17" type="29"/>
<A seq="18" type="6" s="6"/>
<A seq="19" type="7" s="3" v="500"/>
<A seq="20" type="4" s="6" v="500"/>
<A seq="21" type="30"/>
<A seq="22" type="6" s="6"/>
<A seq="23" type="6" s="3"/>
<A seq="24" type="26" s="6"/>
<A seq="25" type="14" s="6" low="0" v="1800" pot="0"/>
</AS>
</G>

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 01/18/2013 :  13:09:41  Show Profile  Reply with Quote
what are the parts you're interested in?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1770 Posts
Posted - 01/18/2013 :  13:10:55  Show Profile  Visit jackv's Homepage  Reply with Quote
once you get the docs into properly formatted ,OPENXML will provide a rowset view

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

whipit
Starting Member

4 Posts
Posted - 01/18/2013 :  13:19:30  Show Profile  Reply with Quote
<G id="561548022" dt="1347139797" pc="9d,8c,2c,Qs,9s">
s="6" name="downts" c="Ks,8d" chips="15307.50"

these are the parts i am interested in extracting.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 01/18/2013 :  13:23:36  Show Profile  Reply with Quote
what all you need to extract
show expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 01/18/2013 :  13:27:09  Show Profile  Reply with Quote
G is the root node, isn't it? If that is what you want to select, there is really nothing to do other than simply select the XML column.
Go to Top of Page

whipit
Starting Member

4 Posts
Posted - 01/19/2013 :  06:35:08  Show Profile  Reply with Quote
I would like it to look something like below



thanks again for all your help
I am trying to learn as fast as i can
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 01/19/2013 :  12:17:24  Show Profile  Reply with Quote
Syntax of your CDATA section does not seem to be correct. It should be something like this
<title>
<![CDATA[Deep Stack (6851218), No Limit Texas Holdem]]>
</title>


Regardless, the query that you can use would be something like shown below. If the data is in a column in a table, instead of using the variable, use the nodes method against that column
SELECT
	ca.value('@id','int') id,
	ca.value('@dt','varchar(32)') dt,
	ca.value('@pc','varchar(64)') pc,
	cb.value('@s','varchar(32)') s,
	cb.value('@name','varchar(32)') name,
	cb.value('@c','varchar(32)') c,
	cb.value('@chips','varchar(32)') chips
FROM
	@XMLVARIABLE.nodes('/G') Ta(ca)
	CROSS APPLY ca.nodes('PS/P') Tb(cb);
Edited by - James K on 01/19/2013 15:01:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 01/19/2013 :  13:45:26  Show Profile  Reply with Quote 

declare @x xml

set @x='<G id="561548022" dt="1347139797" stake="50/100" limit="3" game="1" type="3" seats="10" ver="1" pot="1800" rake="0" pc="9d,8c,2c,Qs,9s">
<title>
<![CDATA[Deep Stack (6851218), No Limit Texas Holdem]]>
</title>
<PS self="3" dealer="5">
<P s="6" name="downts" c="Ks,8d" chips="15307.50" cs="102 M:102.1 B:153.1 #:18 VP:61 PFR:6 AF:7.5 W:17|50 STL:0|50 3B:0| CB:100| N:1400 STK:+1" hs="102 M:102.1 B:153.1 #:18 VP:61 PFR:6 AF:7.5 W:17|50 STL:0|50 3B:0| CB:100| N:1400 STK:+1"/>
<P s="1" name="mooby" c="," chips="3867.50" cs="101 M:25.8 B:38.7 #:18 VP:33 PFR:0 AF:0 W:29|50 STL:|0 3B:0| CB: N:425 STK:-1" hs="101 M:25.8 B:38.7 #:18 VP:33 PFR:0 AF:0 W:29|50 STL:|0 3B:0| CB: N:425 STK:-1"/>
<P s="3" name="kendo" c="Jh,Kc" chips="10860" cs="107 M:72.4 B:108.6 #:18 VP:22 PFR:0 AF:Inf. W:33|0 STL:0| 3B:0| CB:|100 N:-1350 STK:-8" hs="107 M:72.4 B:108.6 #:18 VP:22 PFR:0 AF:Inf. W:33|0 STL:0| 3B:0| CB:|100 N:-1350 STK:-8"/>
<P s="2" name="GANDHI" c="," chips="7060" cs="107 M:47.1 B:70.6 #:18 VP:17 PFR:6 AF:Inf. W:0| STL: 3B:0| CB:100| N:700 STK:-2" hs="107 M:47.1 B:70.6 #:18 VP:17 PFR:6 AF:Inf. W:0| STL: 3B:0| CB:100| N:700 STK:-2"/>
<P s="4" name="MAX" c="," chips="5602.50" cs="102 M:37.4 B:56 #:18 VP:33 PFR:28 AF:3.0 W:17|0 STL:50| 3B:0| CB:0| N:-1075 STK:+1" hs="102 M:37.4 B:56 #:18 VP:33 PFR:28 AF:3.0 W:17|0 STL:50| 3B:0| CB:0| N:-1075 STK:+1"/>
<P s="5" name="kazza" c="," chips="4782.50" cs="104 M:31.9 B:47.8 #:18 VP:28 PFR:0 AF:0 W:29|100 STL:0|50 3B:0| CB:|50 N:-100 STK:-2" hs="104 M:31.9 B:47.8 #:18 VP:28 PFR:0 AF:0 W:29|100 STL:0|50 3B:0| CB:|50 N:-100 STK:-2"/>
</PS>
<AS>
<A seq="1" type="1" s="6" v="50"/>
<A seq="2" type="2" s="1" v="100"/>
<A seq="3" type="27" s="3"/>
<A seq="4" type="3" s="2"/>
<A seq="5" type="4" s="3" v="100"/>
<A seq="6" type="3" s="4"/>
<A seq="7" type="4" s="5" v="100"/>
<A seq="8" type="4" s="6" v="50"/>
<A seq="9" type="6" s="1"/>
<A seq="10" type="28"/>
<A seq="11" type="6" s="6"/>
<A seq="12" type="6" s="1"/>
<A seq="13" type="7" s="3" v="200"/>
<A seq="14" type="3" s="5"/>
<A seq="15" type="4" s="6" v="200"/>
<A seq="16" type="3" s="1"/>
<A seq="17" type="29"/>
<A seq="18" type="6" s="6"/>
<A seq="19" type="7" s="3" v="500"/>
<A seq="20" type="4" s="6" v="500"/>
<A seq="21" type="30"/>
<A seq="22" type="6" s="6"/>
<A seq="23" type="6" s="3"/>
<A seq="24" type="26" s="6"/>
<A seq="25" type="14" s="6" low="0" v="1800" pot="0"/>
</AS>
</G>'

SELECT u.value('../../@id','int') id,
	u.value('../../@dt','varchar(32)') dt,
	u.value('../../@pc','varchar(64)') pc,
	u.value('../../@s','varchar(32)') s,
	u.value('@name','varchar(32)') name,
	u.value('@c','varchar(32)') c,
	u.value('@chips','varchar(32)') chips
FROM @x.nodes('/G/PS/P')t(u)


output
--------------------------------------------------------
id	dt	pc	s	name	c	chips
--------------------------------------------------------
561548022	1347139797	9d,8c,2c,Qs,9s	NULL	downts	Ks,8d	15307.50
561548022	1347139797	9d,8c,2c,Qs,9s	NULL	mooby	,	3867.50
561548022	1347139797	9d,8c,2c,Qs,9s	NULL	kendo	Jh,Kc	10860
561548022	1347139797	9d,8c,2c,Qs,9s	NULL	GANDHI	,	7060
561548022	1347139797	9d,8c,2c,Qs,9s	NULL	MAX	,	5602.50
561548022	1347139797	9d,8c,2c,Qs,9s	NULL	kazza	,	4782.50

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Edited by - visakh16 on 01/19/2013 13:45:50
Go to Top of Page

whipit
Starting Member

4 Posts
Posted - 01/20/2013 :  07:56:20  Show Profile  Reply with Quote
great help. cant thank you all enough.
will be trying all this out today

thanks
again
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 01/21/2013 :  00:08:40  Show Profile  Reply with Quote
welcome
Let us know if you need any more assistance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000