Column values from the XML Column's attributes

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column values from the XML Column's attributes

Author

Topic

B Rajeev
Starting Member

5 Posts

Posted - 2012-03-13 : 13:56:02

Hi,

I require to write the query which will take data from the table having columns, in case the data is not available in these columns then I need to recieve the data from the Column having XML String.I am having a dataset where I am taking the values from the table say xyz.

All the values are coming from xyz table's fields, but in case any field is null then the value has to be retrieved from Field having XML String available in the same table.

e.g., Table "xyz"

RecNo Info Field1 Field2 Field3(XML Content)

1 LMN ABC,YS PQR NULL

2 NULL NULL NULL **[Data Shown below]

<Details>
<Info MobileNo = "(11)9739466865" Address = 'TEST Address' />
<Detail Name="Table Info" Field1="GHI" Field2="TST"/></Details>
<Detail Name="Table Info1" Field1="QTP" Field2="GRS"/></Details>
<Details>

I need the out put as follows

RecNo Info Field1 Field2

1 LMN ABC,YS PQR

2 TestAddress GHI,QTP TST,GRS

Can any one help me on this to write the query so that I can get above results.

i..e, if value is NULL in the column then take the attribute values concatenate them for the respective fields(Like GHI,QTP) and show in the respective fields.

Thanks,

Rajeev.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-13 : 14:31:59

If you have many rows which have nulls and there is corresponding data in the XML, how should/would you associate a specific node of the XML with a specific row?

Regardless, you can shred the XML into tabular form like this:

SELECT
	c.value('Info[1]/@Address','varchar(32)'),
	REPLACE(CAST(c.query('data(Detail/@Field1)') AS VARCHAR(255)),' ',','),
	REPLACE(CAST(c.query('data(Detail/@Field2)') AS VARCHAR(255)),' ',',')
	
FROM @XMLData.nodes('//Details') T(c)

That assumes that the XML is in a variable. If it is in a table, change the FROM clause to

FROM YourTable y
	 CROSS APPLY YourXMLColumn.nodes('//Details') T(c)

The code parses, but I could not test it - your XML is not well-formed.

B Rajeev
Starting Member

5 Posts

Posted - 2012-03-13 : 21:18:49

Thanks sunitabeck,

Here I am giving some more information

Field1 and Field2 has respective information in the attributes of the XML String Column.

Here I am correcting the XML

<Details>
<Info MobileNo = "(11)9739466865" Address = 'TEST Address' />
<Detail Name="Table Info" Field1="GHI" Field2="TST"/>
<Detail Name="Table Info1" Field1="QTP" Field2="GRS"/>
<Details>

Now in case of NULL I tried to pick from the XML but not succeeded

SELECT [Info]
,FirstField = CASE [Field1] WHEN NULL THEN (select field3 from Test.dbo.TestTable where Info=NULL)
ELSE (select field3 from Test.dbo.TestTable where Info=NULL)
END
,SecondField = CASE [Field2] WHEN NULL THEN ' '
ELSE [Field2]
END
,[Field3]
FROM [Test].[dbo].[TestTable]
GO

Can any one help on this.

Thanks,
Rajeev.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 23:16:36

[code]
declare @t table
(
Field1 varchar(50),
Field2 varchar(50),
x xml
)

insert @t
select 'TTP,KP','KKR',null union all
select null,null,'<Details>
<Info MobileNo = "(11)9739466865" Address = "TEST Address" />
<Detail Name="Table Info" Field1="GHI" Field2="TST"/>
<Detail Name="Table Info1" Field1="QTP" Field2="GRS"/>
</Details>' union all
select 'KKR,KP',null,'<Details>
<Info MobileNo = "(11)235345253" Address = "TEST Address1" />
<Detail Name="Table Info" Field1="" Field2="MBM"/>
<Detail Name="Table Info1" Field1="" Field2="GRS"/>
<Detail Name="Table Info1" Field1="" Field2="BJK"/>
</Details>'

select COALESCE(Field1,REPLACE(x.query('data(/Details/Detail/@Field1)').value('.','varchar(50)'),' ',',')) AS Field1,
COALESCE(Field2,REPLACE(x.query('data(/Details/Detail/@Field2)').value('.','varchar(50)'),' ',',')) AS Field2
FROM @t

output
-----------------------------------------------
Field1 Field2
-----------------------------------------------
TTP,KP KKR
GHI,QTP TST,GRS
KKR,KP MBM,GRS,BJK

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

B Rajeev
Starting Member

5 Posts

Posted - 2012-03-14 : 03:28:28

Hi visakh16,

Thanks for the prompt reply.

but how I can include this in the following
i.e., When Offer Options is returning null then I want to add this logic, but while adding the logic it is giving error.

Please help on this.

SELECT
distinct Line.fwi_selectedofferinternal, Line.fwi_lineid,
Line.fwi_name [fwi_lineno],
Line.fwi_useridname [fwi_userid],
LineMaterial.fwi_materialdesignation [designation],
Line.fwi_lineoffesidname [fwi_lineoffesid],
Line.fwi_enterimei [fwi_materialimeino],
Line.fwi_entersimnumber [fwi_entersimnumber],
LineMaterial.fwi_tariff,
LineMaterial.fwi_tariff2,
LineMaterial.fwi_tariff3,
Line.fwi_payeridname [fwi_payerid],
Line.statecode [statecode],
(Select Options.li from (
Select FilteredFWI_listofferoptions.fwi_name li from FilteredFWI_line
JOIN FilteredFWI_lineoffers
ON FilteredFWI_line.fwi_lineid = FilteredFWI_lineoffers.fwi_lineid
JOIN FilteredFWI_listofferoptions
ON FilteredFWI_listofferoptions.fwi_lineofferidid = FilteredFWI_lineoffers.fwi_lineoffersid
where FilteredFWI_line.fwi_lineid = Line.fwi_lineid) Options
FOR XML PATH('') ) [Offer Option]

FROM FilteredAccount as account
LEFT JOIN FilteredContact Contact ON Contact.parentcustomerid = account.accountid
LEFT JOIN FilteredFwi_Line Line ON Line.fwi_clientid = account.accountid
LEFT Join FilteredFWI_linematerial LineMaterial
ON Line.fwi_lineid = LineMaterial.fwi_linenoid and LineMaterial.FWI_MaterialStatus = 1

Thanks,
Rajeev.

B Rajeev
Starting Member

5 Posts

Posted - 2012-03-14 : 07:47:15

Hi Visakh,

The code you provided works excellently, but I am getting commas when the XML Attribute's value has space for example "Field2="TST"

has the value as follows:-

Field2="TST ABc"

Code provided by you is as follows

declare @t table
(
Field1 varchar(50),
Field2 varchar(50),
x xml
)

insert @t
select 'TTP,KP','KKR',null union all
select null,null,'<Details>
<Info MobileNo = "(11)9739466865" Address = "TEST Address" />
<Detail Name="Table Info" Field1="GHI" Field2="TST"/>
<Detail Name="Table Info1" Field1="QTP" Field2="GRS"/>
</Details>' union all
select 'KKR,KP',null,'<Details>
<Info MobileNo = "(11)235345253" Address = "TEST Address1" />
<Detail Name="Table Info" Field1="" Field2="MBM"/>
<Detail Name="Table Info1" Field1="" Field2="GRS"/>
<Detail Name="Table Info1" Field1="" Field2="BJK"/>
</Details>'

select COALESCE(Field1,REPLACE(x.query('data(/Details/Detail/@Field1)').value('.','varchar(50)'),' ',',')) AS Field1,
COALESCE(Field2,REPLACE(x.query('data(/Details/Detail/@Field2)').value('.','varchar(50)'),' ',',')) AS Field2
FROM @t

Please help on this.

Thanks,
Rajeev.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources