Extracting Attributes from XML Fields in SQL Server 2008 Table -


i have table several columns, 1 of xml column. not have namespace use in query. xml data same structure records.

contrived data

create table #temp (id int, name varchar(32), xml_data xml)  insert #temp values (1, 'one',   '<data><info x="42" y="99">red</info></data>'), (2, 'two',   '<data><info x="27" y="72">blue</info></data>'), (3, 'three', '<data><info x="16" y="51">green</info></data>'), (4, 'four',  '<data><info x="12" y="37">yellow</info></data>')

desired results

name    info.x   info.y   info -----   -------  -------  ------- 1       42       99     red 2       27       72     blue 3     16       51     green 4      12       37     yellow

partially works

select name, xml_data.query('/data/info/.').value('.', 'varchar(10)') [info]   #temp

it returns name , info columns. cannot figure out how extract attribute values without using namespace. instance, following queries returns errors:

query 1

select name, xml_data.query('/data/info/@x') [info]   #temp  msg 2396, level 16, state 1, line 12 xquery [#temp.xml_data.query()]: attribute may not appear outside of element

query 2

select name, xml_data.value('/data/info/@x', 'int') [info]   #temp  msg 2389, level 16, state 1, line 12 xquery [#temp.xml_data.value()]: 'value()' requires singleton (or empty sequence), found operand of type 'xdt:untypedatomic *'

query 3

select name, xml_data.query('/data/info/.').value('@x', 'int') [info]   #temp  msg 2390, level 16, state 1, line 9 xquery [value()]: top-level attribute nodes not supported

question

how write query return regular column data, , element + attribute values xml column in same table?

just after posted question, stumbled across answer. don't know why couldn't find in prior searches. answer looking for. here query works:

query

select name       ,xml_data.value('(/data/info/@x)[1]', 'int') [info.x]       ,xml_data.value('(/data/info/@y)[1]', 'int') [info.y]       ,xml_data.value('(/data/info/.)[1]', 'varchar(10)') [info]   #temp

result

name     info.x    info.y    info -------  --------  --------  --------- 1         42        99     red 2         27        72     blue 3       16        51     green 4        12        37     yellow

.

------ edit [2014-01-29] ------

i found case worth adding answer. given multiple <info> elements within <data> element, possible return <info> nodes using cross apply:

create table #temp (id int, name varchar(32), xml_data xml)  insert #temp values (1, 'one',   '<data><info x="42" y="99">red</info><info x="43" y="100">pink</info></data>'), (2, 'two',   '<data><info x="27" y="72">blue</info><info x="28" y="73">light blue</info></data>'), (3, 'three', '<data><info x="16" y="51">green</info><info x="17" y="52">orange</info></data>'), (4, 'four',  '<data><info x="12" y="37">yellow</info><info x="13" y="38">purple</info></data>')  select name       ,c.value('@x', 'int') [info.x]       ,c.value('@y', 'int') [info.y]       ,c.value('.', 'varchar(10)') [info] #temp cross apply      #temp.xml_data.nodes('data/info') x(c)  drop table #temp

this example returns following dataset:

name      info.x      info.y      info --------- ----------- ----------- ---------- 1       42          99          red 1       43          100         pink 2       27          72          blue 2       28          73          light blue 3     16          51          green 3     17          52          orange 4      12          37          yellow 4      13          38          purple

Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more

C# WinForm - loading screen -

i ask how make loading screen (just picture or something) appears while program being loaded, , disappears when program has finished loading. in fancier versions, have seen process bar (%) displayed. how can have that, , how calculate % show on it? i know there form_load() event, not see form_loaded() event, or % property / attribute anywhere. all need create 1 form splash screen , show before main start showing landing page , close splash once landing page loaded. class splashform { //delegate cross thread call close private delegate void closedelegate(); //the type of form displayed splash screen. private static splashform splashform; static public void showsplashscreen() { // make sure launched once. if (splashform != null) return; thread thread = new thread(new threadstart(splashform.showform)); thread.isbackground = true; thread.setapartmentstate(apartmentstate.sta); thread....
Read more