Getting data from XML column - SQL Server Forums

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)

Getting data from XML column

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-03-11 : 10:47:41

hello
I have a table with XML column

select columnsupdated from BinHistoryAudit

if I click on XML i see

<Fields>
<Field Name="Bin_Scheme" />
<Field Name="Bin_Title" />
</Fields>

I need to get from TSQL:

Updated_field
----------
Bin_Scheme
Bin_Title

I tried this select:

SELECT
ref.value ('[Field Name]', 'nvarchar(364)') as [Updated_Field]
FROM BinHistoryAudit CROSS APPLY Columnsupdated.nodes ('Fields/[Field Name]') R(ref)

But get this error:

Msg 2256, Level 16, State 1, Line 4
XQuery [BinHistoryAudit.ColumnsUpdated.nodes()]: Syntax error near '[', expected a "node test".

Any Ideas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:40:54

[code]
SELECT
ref.value ('@Name', 'nvarchar(364)') as [Updated_Field]
FROM BinHistoryAudit
CROSS APPLY Columnsupdated.nodes ('/Fields/Field') R(ref)
[/code]

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

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-03-11 : 11:55:05

Thank you sooooo much....i have been scouring the internet for a solution with no luck.....

:)
Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 12:04:21

welcome

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

gnsitaramudu
Starting Member

1 Post

Posted - 2011-06-16 : 00:06:22

Hello Visakh,

I have a XML like

<category type="ssrs">
<book>SQL Server 2005</book>
<lang>en</lang>
<book>SQL Server 2008</book>
<lang>en-us</lang>
</category>

Output has to be:

Category book lang
-----------------------------------
ssrs SQL Server 2005 en
ssrs SQL Server 2008 en-us

I need this recordset without using any cursors or looping statements

Is there maybe someone who can help me with this?

regards,
rams..

GN Sita Ramudu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 00:02:24

quote:
Originally posted by gnsitaramudu

Hello Visakh,

I have a XML like

<category type="ssrs">
<book>SQL Server 2005</book>
<lang>en</lang>
<book>SQL Server 2008</book>
<lang>en-us</lang>
</category>

Output has to be:

Category book lang
-----------------------------------
ssrs SQL Server 2005 en
ssrs SQL Server 2008 en-us

I need this recordset without using any cursors or looping statements

Is there maybe someone who can help me with this?

regards,
rams..

GN Sita Ramudu

see below illustration


declare @x xml

set @x='<category type="ssrs">
  <book>SQL Server 2005</book>
  <lang>en</lang>
  <book>SQL Server 2008</book>
  <lang>en-us</lang>
</category>'

select distinct p.q.value('./@type[1]','varchar(10)') as category,
a.b.value('.','varchar(10)') as book,
m.n.value('.','varchar(10)') as lang
from @x.nodes('/category')p(q)
cross apply q.nodes('book')a(b)
cross apply q.nodes('lang')m(n)


output
------------------------------
category	book	        lang
ssrs	        SQL Server	en
ssrs	        SQL Server	en-us

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 00:30:57

quote:
Originally posted by NyiNyi

Hi I want to retrieve dat a from this xml format column from my table.
I want to get the the value of <Item id="SN"> of value and Product Value.
How to get it?
<data>
<tmpl id="A">
<repeat seq="1>
<Item ID="Date><value>09/09/12</value>
<tmpl id="t_se">
<repeat seq ="1"><Item id="Item_ID"><value>1</value></repeat>
</tmpl>
<tmpl id="t_prod">
<repeat seq ="1">
<item id="SN"><value>1</value></item>
<item id="Prod"><value>apple</value></item>
</repeat>
<repeat seq="2">
<item id="SN"><value>2</value></item>
<item id="Prod"><value>Orange</value></item>
</repeat>
</tmpl>
</repeat>
</tmpl>
</data>

see illustration below

your XML was not well formed though

i've corrected it

see



declare @x xml

set @x='<data>
			<tmpl id="A">
				<repeat seq="1">
					<Item ID="Date">
						<value>09/09/12</value>
						<tmpl id="t_se">
							<repeat seq ="1">
								<Item id="Item_ID">
									<value>1</value>
								</Item>
							</repeat>
						</tmpl>
						<tmpl id="t_prod">
							<repeat seq ="1">
								<item id="SN">
									<value>1</value>
								</item>
								<item id="Prod">
									<value>apple</value>
								</item>
							</repeat>
							<repeat seq="2">
								<item id="SN">
									<value>2</value>
								</item>
								<item id="Prod">
									<value>Orange</value>
								</item>
							</repeat>
						</tmpl>
					</Item>
				</repeat>
			</tmpl>
		</data>'


select  p.q.value('(./item[@id="SN"]/value)[1]','varchar(10)') AS SN ,
p.q.value('(./item[@id="Prod"]/value)[1]','varchar(10)') AS Prod
from @x.nodes('/data/tmpl/repeat/Item/tmpl/repeat')p(q)
where q.exist('./item[@id="Prod"]')=1
or q.exist('./item[@id="SN"]')=1



SN	Prod
--------------
1	apple
2	Orange

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

NyiNyi
Starting Member

3 Posts

Posted - 2012-03-12 : 01:10:26

Thanks you very much visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:08:55

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources