tsql - Using XML with Transact-SQL -



tsql - Using XML with Transact-SQL -

i'm working in sql server 2008 r2. have 20 databases same structure. i'm not exclusively sure went wrong code. if point out error or point me few resources on subject appreciated.

the code i'm running is:

create table #temp ([client] varchar(100), [language_code] char(100)) exec sp_msforeachdb ' if ''?'' ''%'' , exists(select * ?.sys.tables t t.name = ''licenceinfoes'') begin declare @myxml xml declare @lang_code char(100) set @lang_code = ''(select a.b.value''(''laguages[1]'',''char(100)'')'' @myxml.nodes(''licence'') a(b))'' insert #temp select ''?'', @lang_code end ' select * #temp drop table #temp

the end result error:

msg 102, level 15, state 1, line 7 wrong syntax near 'laguages[1]'.

the desired output table 2 columns this:

<db_name> <language_codes> name1 en-gb, en-us

the original query works fine when executed on own , gives me info i'm looking run against of dbs @ 1 time save me time , allow automation of process.

declare @myxml xml set @myxml = (select licencedata licenceinfoes id='1001') select a.b.value('laguages[1]', 'char(100)') language_codes @myxml.nodes('licence') a(b)

and sample xml stored within 1 of dbs

<licence xmlns:xsd="http://www.w3.org/2001/xmlschema" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"> <clients /> <lastupdated>2014-06-03t15:59:46.9831585z</lastupdated> <customerid>9999999</customerid> <customername>xxxxx</customername> <contactemail>someone@somewhere.com</contactemail> <type>commercial</type> <languages> <language>en-gb</language> <language>en-us</language> </languages> <defaultlanguagepack>en-gb</defaultlanguagepack>

any help here appreciated.

edit

after trying out few things, found error located.

the error in @myxml variable:

set @myxml = ''(select licencedata t.licenceinfoes)''

changing t.licenceinfoes ?..licenceinfoes fixed issue having.

does help?

declare @tbl table (id int not null, xmlcontent xml) insert @tbl values(1, '<licence xmlns:xsd="http://www.w3.org/2001/xmlschema" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"> <clients /> <lastupdated>2014-06-03t15:59:46.9831585z</lastupdated> <customerid>9999999</customerid> <customername>xxxxx</customername> <contactemail>someone@somewhere.com</contactemail> <type>commercial</type> <languages> <language>en-gb</language> <language>en-us</language> </languages> <defaultlanguagepack>en-gb</defaultlanguagepack></licence>') select id, xt.xc.value('(.)[1]', 'varchar(50)') @tbl cross apply xmlcontent.nodes('/licence/languages/language') xt(xc)

this produces output of:

id (no column name) 1 en-gb 1 en-us

tsql xml-parsing sql-server-2008-r2

Comments

Post a Comment

Popular posts from this blog

php - Android app custom user registration and login with cookie using facebook sdk -

php - Android app custom user registration and login with cookie using facebook sdk - i developing android application in users need register , log in using facebook. approximately, flow. open app first time, authenticate facebook, data, send info server create them business relationship in application, start session new business relationship in server, cookie perform later interactions. so far good. what need know wich info have send server, can able identify users when log in seccond time. because need start session in server , send cookie android app farther interactions. i'm using php server side language. i thinking getting getaccesstoken() in android facebook sdk, pass server on business relationship creation , storing in android app. but can't manage how login users on sec time utilize app. any suggestions ? in advance ! to uniquely identify user within app, should create request /me , user's id. guaranteed unique , remain con
Read more

django - Access session in user model .save() -

django - Access session in user model .save() - is possible access current session in user model .save()? pseudo code of want achieve: # users.models.py def save(self, *args, **kwargs): created = true if self.pk: created = false super(abstractuser, self).save(*args, **kwargs) # post-save if created: look_for_invite_in_session_and_register_if_found(self, session) seems wrong in architecture. shouldn't access request in models layer. work request must done in view. can this: user, created = abstractuser.objects.get_or_create(name=name) if created: look_for_invite_in_session_and_register_if_found(user, request.session) django session
Read more

php - .htaccess Multiple Rewrite Rules / Prioritizing -

php - .htaccess Multiple Rewrite Rules / Prioritizing - here current htaccess file: rewriteengine on rewritecond %{http_host} !^www\.website\.com [nc] rewritecond %{http_host} ([^.]+)\.website\.com [nc] rewriterule ^(.*)$ http://www.website.com/gotourl.php?urlid=?%1 [l] rewriterule ^(\w+)$ ./gotourl.php?urlid=$1 [nc,l] rewriterule ^(\w+)/$ ./gotourl.php?urlid=$1 [nc,l] the website in question url shortener i'm working on testing/developing , personal use. above code may not ideal i'm not familiar .htaccess , made via googling , testing. want url shortening aspect: http://website.com/1234 -> http://website.com/gotourl.php?urlid=1234 http://website.com/1234/ -> http://website.com/gotourl.php?urlid=1234 the problem cannot figure out how ignore directories. want specific directories go specific pages: http://website.com/img (or) http://website.com/img/ -> http://website.com/img.php http://website.com/img/1234 (or) http://website.com/
Read more