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
 General SQL Server Forums
 New to SQL Server Programming
 exstract part of xml field

Author  Topic 

whipit
Starting Member

4 Posts

Posted - 2013-01-18 : 11:07:49
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

52326 Posts

Posted - 2013-01-18 : 13:09:41
what are the parts you're interested in?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-18 : 13:10:55
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 - 2013-01-18 : 13:19:30
<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

52326 Posts

Posted - 2013-01-18 : 13:23:36
what all you need to extract
show expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-18 : 13:27:09
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 - 2013-01-19 : 06:35:08
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-19 : 12:17:24
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);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-19 : 13:45:26
[code]
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

[/code]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

whipit
Starting Member

4 Posts

Posted - 2013-01-20 : 07:56:20
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

52326 Posts

Posted - 2013-01-21 : 00:08:40
welcome
Let us know if you need any more assistance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -