XML query

Question

XML query

JoyKing

Ten Centuries

Points: 1106
More actions
June 29, 2010 at 6:03 am

#236982

Hi Everyone,
I want to extract data from xml column.
Xml data:
<ITEM RESPONDENT_ID="2" Business_Account_Name="ElectroDom" Category="Director" />
Required Output:
Respondent_id Business_Account_Name Category
2 Electrodom Director
Note: I cannot specify the column names as it varies from one row to another row. If I pass one xml filed I should get column name along with the values.
Thanks in advance,
Keerthy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

JoyKing (6/29/2010)
Xml data:
<ITEM RESPONDENT_ID="2" Business_Account_Name="ElectroDom" Category="Director" />
Required Output:
Respondent_id Business_Account_Name Category
2 Electrodom Director

I've never seen XML that looks like that. . .

DECLARE @data XML

SET @data ='<ITEM><RESPONDENT_ID>2</RESPONDENT_ID><Business_Account_Name>ElectroDom</Business_Account_Name><Category>Director</Category></ITEM>'

SELECT @data.value('(/*/RESPONDENT_ID)[1]', 'NVARCHAR(10)') RESPONDENT_ID

,@Data.value('(/*/Business_Account_Name)[1]', 'NVARCHAR(10)') Business_Account_Name

,@Data.value('(/*/Category)[1]', 'NVARCHAR(10)') Category

If you data is how you've laid it out instead of how I've laid it out, this query won't work.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JoyKing Ten Centuries Points: 1106 More actions · Answer 2

Hi,

Thanks for your reply...

If I am not wrong I will get the required output from the below code, but the challenge here is I cannot specify the column names and data type because its dynamic data. So, is there any way to get the required data without defining the column name and datatype.

DECLARE @x XML

SET @x = '<ITEMS>

</ITEMS>'

select x.item.value('@RESPONDENT_ID[1]', ' [Numeric] ') As RESPONDENT_ID,

x.item.value('@Business_Account_Name[1]', ' [varchar](500) ') AS Business_Account_Name,x.item.value('@Category[1]', ' [varchar](500) ')

As Category FROM @x.nodes('//ITEMS/ITEM') AS x(item)

Thanks & regards,

Keerthy

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

I'm still learning SQL, so what I'll show below is definately not the best way to do this :hehe:

DECLARE @xml AS XML

SET @xml ='<ITEM RESPONDENT_ID="2" Business_Account_Name="ElectroDom" Category="Director" />'

DECLARE @pseudoxml VARCHAR(8000)

SET @pseudoxml = CAST(@xml AS VARCHAR(8000))

DECLARE @Delimeter CHAR(1)

SET @Delimeter = '"'

DECLARE @table TABLE(

id INT IDENTITY,

data VARCHAR(50))

DECLARE @data VARCHAR(50)

DECLARE @StartPos INT,

@Length INT

WHILE Len(@pseudoxml) > 0

BEGIN

SET @StartPos = Charindex(@Delimeter, @pseudoxml)

IF @StartPos < 0

SET @StartPos = 0

SET @Length = Len(@pseudoxml) - @StartPos - 1

IF @Length < 0

SET @Length = 0

IF @StartPos > 0

BEGIN

SET @data = Substring(@pseudoxml, 1, @StartPos - 1)

SET @pseudoxml = Substring(@pseudoxml, @StartPos + 1,

Len(@pseudoxml) - @StartPos)

END

ELSE

BEGIN

SET @data = @pseudoxml

SET @pseudoxml = ''

END

INSERT @table

(data)

VALUES(@data)

END

DECLARE @intermiediate TABLE(

id INT IDENTITY,

columnname VARCHAR(50),

data VARCHAR(50))

INSERT INTO @intermiediate

(columnname,

data)

SELECT t2.data,

t1.data

FROM @table AS t1

LEFT OUTER JOIN @table AS t2

ON ( t1.id = t2.id + 1 )

DECLARE @final TABLE(

columnname VARCHAR(50),

data VARCHAR(50))

INSERT INTO @final

(columnname,

data)

SELECT REPLACE(REPLACE(REPLACE(Substring(columnname, 1,

Charindex('=', columnname) - 1)

, '<',

''), 'ITEM', ''), ' ', ''),

data

FROM @intermiediate

WHERE NOT Abs(id) % 2 = 1

SELECT *

FROM @final

That gives you the output: -

/*

columnname data

-------------------------------------------------- --------------------------------------------------

RESPONDENT_ID 2

Business_Account_Name ElectroDom

Category Director

*/

Finally, you just need to swap the row "columnname" to be a column. Pretty sure you can do this with a pivot, but not got that far yet, so from here I'm out. Pivots are on my list of things to learn 😉

*edit*

If it was me, right now I think I'd write some dynamic-sql that creates a table from the "@final" table, but pretty sure a pivot is better.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Using this sort of approach, you should be able to dynamically pivot the data

DECLARE @data XML

SET @data ='<ITEM><RESPONDENT_ID>2</RESPONDENT_ID><Business_Account_Name>ElectroDom</Business_Account_Name><Category>Director</Category></ITEM>'

SELECT r.value('local-name(.)','VARCHAR(100)') AS Name,

r.value('.','VARCHAR(100)') AS Value

FROM @data.nodes('/ITEM/*') AS x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537