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.
Author |
Topic |
whipit
Starting Member
4 Posts |
Posted - 2013-01-18 : 11:07:49
|
Hi Allhope someone can help meI 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 advanceBarry<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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 viewJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 13:23:36
|
what all you need to extractshow expected output------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
whipit
Starting Member
4 Posts |
Posted - 2013-01-19 : 06:35:08
|
I would like it to look something like belowthanks again for all your helpI am trying to learn as fast as i can |
|
|
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 columnSELECT 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)') chipsFROM @XMLVARIABLE.nodes('/G') Ta(ca) CROSS APPLY ca.nodes('PS/P') Tb(cb); |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-19 : 13:45:26
|
[code]declare @x xmlset @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)') chipsFROM @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.50561548022 1347139797 9d,8c,2c,Qs,9s NULL mooby , 3867.50561548022 1347139797 9d,8c,2c,Qs,9s NULL kendo Jh,Kc 10860561548022 1347139797 9d,8c,2c,Qs,9s NULL GANDHI , 7060561548022 1347139797 9d,8c,2c,Qs,9s NULL MAX , 5602.50561548022 1347139797 9d,8c,2c,Qs,9s NULL kazza , 4782.50[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 todaythanksagain |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 00:08:40
|
welcomeLet us know if you need any more assistance------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|