CODEWITHSUNDEEP
t-sqlxml-parsingsql-server-2008-r2
user3753561
user3753561

Reputation: 13

Using XML with Transact-SQL

I'm working in SQL Server 2008 R2. I have about 20 databases all with the same structure. I'm not entirely sure where I went wrong with my code. If someone could point out my mistake or point me to a few resources on the subject it would be appreciated.

The code I'm running is:

create table #temp 
([Client] varchar(100), [Language_Code] char(100))

exec sp_msforeachdb '
if ''?'' like ''%'' and exists(select * from ?.sys.tables t where t.name =  ''LicenceInfoes'')
begin
DECLARE @MyXML XML
Declare @lang_code char(100)

set @lang_code = ''(SELECT a.b.value''(''Laguages[1]'',''char(100)'')'' FROM @MyXML.nodes(''Licence'') a(b))''
insert into #temp select ''?'', @lang_code  
end
'

select * from #temp

drop table #temp

The end result is this error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'Laguages[1]'.

The desired output would be a table with 2 columns like this:

<DB_Name> <Language_Codes>
  Name1      en-GB, en-US

The original query works fine when executed on its own and gives me the data I'm looking for but I would like to run it against all of the DBs at once to save me some time and allow for automation of the process.

DECLARE @MyXML XML

SET @MyXML = (select LicenceData from LicenceInfoes where Id='1001')

SELECT 
    a.b.value('Laguages[1]', 'char(100)') AS Language_Codes 
FROM 
    @MyXML.nodes('Licence') a(b)

and the sample XML stored within one of the 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>[email protected]</ContactEmail>
  <Type>Commercial</Type>
  <Languages>
    <Language>en-GB</Language>
    <Language>en-US</Language>
  </Languages>
  <DefaultLanguagePack>en-GB</DefaultLanguagePack>

Any help here is appreciated.

EDIT

After trying out a few things, I found where the error is located.

The error is in the @MyXML variable:

SET @MyXML = ''(select LicenceData from t.LicenceInfoes)''

Changing t.LicenceInfoes to ?..LicenceInfoes fixed the issue I was having.

Upvotes: 1

Views: 127

Answers (4)

user3753561
user3753561

Reputation: 13

After trying a few of your options, I ended up with the following error:

Msg 208, Level 16, State 1, Line 7
Invalid object name 'LicenceInfoes'.

I realized that I made a mistake in the part of the code where I set the @MyXML variable.

it was:

SET @MyXML = (select LicenceData from LicenceInfoes)

adding the "?.." before the table name fixed it.

SET @MyXML = (select LicenceData from ?..LicenceInfoes)

I ended up updating my code thanks to marc_s. Now it looks like this:

create table Tbl (Client varchar(max), Language_Code varchar(max))
exec sp_msforeachdb '
    if ''?'' like ''%'' and exists(select * from ?.sys.tables t where t.name =  ''LicenceInfoes'')

begin
SET QUOTED_IDENTIFIER ON

INSERT INTO Tbl

SELECT
    ''?'', 
    a.b.value(''(.)[1]'',''varchar(100)'') AS Language_Codes 
FROM ?..LicenceInfoes 
cross apply LicenceData.nodes(''/Licence/Laguages/Language'') a(b)
end'

select * from Tbl

drop table Tbl

The output of this is exactly like what marc_s has in his example and this is what I wanted.

Thanks all for the replies and the help!

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

I think this might help.

exec sp_msforeachdb '
if ''?'' like ''%'' and exists(select * from ?.sys.tables t where t.name =  ''LicenceInfoes'')
begin
DECLARE @MyXML XML
Declare @lang_code char(100)
SET @MyXML = (select LicenceData from t.LicenceInfoes)
set @lang_code = (SELECT a.b.value(''Laguages[1]'',''char(100)'') FROM @MyXML.nodes(''Licence'') a(b))
insert into #temp select ''?'', @lang_code  
end'

I don't understand the meaning of if ''?'' like ''%'' but, now, the query is syntactically correct.

Upvotes: 0

CailinP
CailinP

Reputation: 234

I believe your error results from the misspelling of Laguages[1] on line 7. Change it to Languages[1] and it should produce the correct output.

Upvotes: 0

marc_s
marc_s

Reputation: 754408

Does this help?

DECLARE @Tbl TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @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>[email protected]</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)')
FROM 
    @Tbl
CROSS APPLY
    XmlContent.nodes('/Licence/Languages/Language') AS XT(XC)

This produces an output of:

ID  (No column name)
1   en-GB
1   en-US

Upvotes: 1

Related Questions