Welcome to dbFreaks.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Simple XML column Query Question?

  
   Database Help (Home) -> XML RSS
Next:  problem with Bind Variables  
Author Message
kevin

External


Since: Sep 30, 2008
Posts: 3



(Msg. 1) Posted: Tue Sep 30, 2008 2:35 pm
Post subject: Simple XML column Query Question?
Archived from groups: microsoft>public>sqlserver>xml (more info?)
I have an XML column that contains multiple records and am looking for
a way to get all of the values using a.value like query. Below is some
TSQL that illustrates my question. Thanks for checking this out.

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


Set NoCount ON

If OBJECT_ID('tempdb..#TmpTest') is Not NULL
Drop Table #TmpTest
GO

Create Table #TmpTest (TmpTestID int identity(1,1) Not NULL,Why XML
NOT NULL)

Insert into #TmpTest(Why) Values ('<Whys><Why
fieldName="Prmr_Ad_Reason_1" fieldValue="" /><Why
fieldName="Chd_Agent_Bank" fieldValue="0100" /><Why
fieldName="Chd_Status_Reason_Code" fieldValue="05" /><Why
fieldName="Chd_Portfolio_No" fieldValue="00001" /><Why
fieldName="Chd_Del_No_Cycles" fieldValue="3" /><Why
fieldName="Chd_Random_Digits" fieldValue="35" /><Why
fieldName="Chd_External_Status" fieldValue="C" /><Why
fieldName="Chd_Current_Balance" fieldValue="21529.2500" /><Why
fieldName="Chd_Telephone_Number" fieldValue="04136647326" /></Whys>')
Insert into #TmpTest(Why) Values ('<Whys><Why
fieldName="Chd_Del_No_Cycles" fieldValue="2" /><Why
fieldName="Chd_Del_No_Cycles" fieldValue="C" /></Whys>')


--
XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
-- This works great... but I need to be able do do this on a select on
a table like below
--
XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
Declare @Test XML

Select @test = (
Select Why
From #TmpTest ph
Where TmpTestID = 1
)

SELECT Whys.Why.value('./@fieldName', 'varchar(100)') as fieldName,
Whys.Why.value('./@fieldValue', 'varchar(7000)') as fieldValue
FROM @test.nodes('/Whys/Why')
Whys(Why)
--
XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
-- This query woks great but only pulls the first record within the
Why element... I would like to see
-- 8 records come back from this with a tmpTestID of 1 and 2 with a
tmpTestID of 2
--
XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
Select
ph.tmpTestID,
ph.Why.value('(/Whys/Why/@fieldName)[1]','varchar(100)') as
FieldName,
ph.Why.value('(/Whys/Why/@fieldValue)[1]','varchar(7000)') as
FieldValue
From #TmpTest ph

 >> Stay informed about: Simple XML column Query Question? 
Back to top
Login to vote
Bob

External


Since: Feb 08, 2005
Posts: 165



(Msg. 2) Posted: Wed Oct 01, 2008 3:37 am
Post subject: RE: Simple XML column Query Question? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Try this:

SELECT tmpTestId,
Whys.Why.value('./@fieldName', 'varchar(100)') as fieldName,
Whys.Why.value('./@fieldValue', 'varchar(7000)') as fieldValue
FROM #TmpTest
CROSS APPLY Why.nodes('/Whys/Why') Whys(Why)

"kevin@teamstephens.com" wrote:

> I have an XML column that contains multiple records and am looking for
> a way to get all of the values using a.value like query. Below is some
> TSQL that illustrates my question. Thanks for checking this out.
>
> -------------------------------------------------------------------------------------------------------------------------------
>
>
> Set NoCount ON
>
> If OBJECT_ID('tempdb..#TmpTest') is Not NULL
> Drop Table #TmpTest
> GO
>
> Create Table #TmpTest (TmpTestID int identity(1,1) Not NULL,Why XML
> NOT NULL)
>
> Insert into #TmpTest(Why) Values ('<Whys><Why
> fieldName="Prmr_Ad_Reason_1" fieldValue="" /><Why
> fieldName="Chd_Agent_Bank" fieldValue="0100" /><Why
> fieldName="Chd_Status_Reason_Code" fieldValue="05" /><Why
> fieldName="Chd_Portfolio_No" fieldValue="00001" /><Why
> fieldName="Chd_Del_No_Cycles" fieldValue="3" /><Why
> fieldName="Chd_Random_Digits" fieldValue="35" /><Why
> fieldName="Chd_External_Status" fieldValue="C" /><Why
> fieldName="Chd_Current_Balance" fieldValue="21529.2500" /><Why
> fieldName="Chd_Telephone_Number" fieldValue="04136647326" /></Whys>')
> Insert into #TmpTest(Why) Values ('<Whys><Why
> fieldName="Chd_Del_No_Cycles" fieldValue="2" /><Why
> fieldName="Chd_Del_No_Cycles" fieldValue="C" /></Whys>')
>
>
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> -- This works great... but I need to be able do do this on a select on
> a table like below
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> Declare @Test XML
>
> Select @test = (
> Select Why
> From #TmpTest ph
> Where TmpTestID = 1
> )
>
> SELECT Whys.Why.value('./@fieldName', 'varchar(100)') as fieldName,
> Whys.Why.value('./@fieldValue', 'varchar(7000)') as fieldValue
> FROM @test.nodes('/Whys/Why')
> Whys(Why)
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> -- This query woks great but only pulls the first record within the
> Why element... I would like to see
> -- 8 records come back from this with a tmpTestID of 1 and 2 with a
> tmpTestID of 2
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> Select
> ph.tmpTestID,
> ph.Why.value('(/Whys/Why/@fieldName)[1]','varchar(100)') as
> FieldName,
> ph.Why.value('(/Whys/Why/@fieldValue)[1]','varchar(7000)') as
> FieldValue
> From #TmpTest ph
>

 >> Stay informed about: Simple XML column Query Question? 
Back to top
Login to vote
kevin

External


Since: Sep 30, 2008
Posts: 3



(Msg. 3) Posted: Wed Oct 01, 2008 6:17 am
Post subject: Re: Simple XML column Query Question? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Very nice.... Now I need to wrap my head around that Cross Apply.
Thank you so much for both of your help.... Martin you are correct
there should be 9.
 >> Stay informed about: Simple XML column Query Question? 
Back to top
Login to vote
kevin

External


Since: Sep 30, 2008
Posts: 3



(Msg. 4) Posted: Wed Oct 01, 2008 7:14 am
Post subject: Re: Simple XML column Query Question? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Very nice.... Now I need to wrap my head around that Cross Apply.
Thank you so much for both of your help.... Martin you are correct
there should be 9.
 >> Stay informed about: Simple XML column Query Question? 
Back to top
Login to vote
Martin Honnen

External


Since: Apr 20, 2007
Posts: 47



(Msg. 5) Posted: Wed Oct 01, 2008 7:25 am
Post subject: Re: Simple XML column Query Question? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
kevin RemoveThis @teamstephens.com wrote:
> I have an XML column that contains multiple records and am looking for
> a way to get all of the values using a.value like query. Below is some
> TSQL that illustrates my question. Thanks for checking this out.
>
> -------------------------------------------------------------------------------------------------------------------------------
>
>
> Set NoCount ON
>
> If OBJECT_ID('tempdb..#TmpTest') is Not NULL
> Drop Table #TmpTest
> GO
>
> Create Table #TmpTest (TmpTestID int identity(1,1) Not NULL,Why XML
> NOT NULL)
>
> Insert into #TmpTest(Why) Values ('<Whys><Why
> fieldName="Prmr_Ad_Reason_1" fieldValue="" /><Why
> fieldName="Chd_Agent_Bank" fieldValue="0100" /><Why
> fieldName="Chd_Status_Reason_Code" fieldValue="05" /><Why
> fieldName="Chd_Portfolio_No" fieldValue="00001" /><Why
> fieldName="Chd_Del_No_Cycles" fieldValue="3" /><Why
> fieldName="Chd_Random_Digits" fieldValue="35" /><Why
> fieldName="Chd_External_Status" fieldValue="C" /><Why
> fieldName="Chd_Current_Balance" fieldValue="21529.2500" /><Why
> fieldName="Chd_Telephone_Number" fieldValue="04136647326" /></Whys>')
> Insert into #TmpTest(Why) Values ('<Whys><Why
> fieldName="Chd_Del_No_Cycles" fieldValue="2" /><Why
> fieldName="Chd_Del_No_Cycles" fieldValue="C" /></Whys>')
>
>
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> -- This works great... but I need to be able do do this on a select on
> a table like below
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> Declare @Test XML
>
> Select @test = (
> Select Why
> From #TmpTest ph
> Where TmpTestID = 1
> )
>
> SELECT Whys.Why.value('./@fieldName', 'varchar(100)') as fieldName,
> Whys.Why.value('./@fieldValue', 'varchar(7000)') as fieldValue
> FROM @test.nodes('/Whys/Why')
> Whys(Why)
> --
> XxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx
> -- This query woks great but only pulls the first record within the
> Why element... I would like to see
> -- 8 records come back from this with a tmpTestID of 1 and 2 with a
> tmpTestID of 2

I think there should be nine not eight records for tmpTestID 1.
Here are two sample queries making use of the CROSS APPLY operator to
extract the records you are looking for:

SELECT Whys.Why.value('./@fieldName', 'varchar(100)') as fieldName,
Whys.Why.value('./@fieldValue', 'varchar(7000)') as fieldValue
FROM #TmpTest AS T CROSS APPLY T.Why.nodes('/Whys/Why') Whys(Why)
WHERE T.TmpTestID = 1

SELECT Whys.Why.value('./@fieldName', 'varchar(100)') as fieldName,
Whys.Why.value('./@fieldValue', 'varchar(7000)') as fieldValue
FROM #TmpTest AS T CROSS APPLY T.Why.nodes('/Whys/Why') Whys(Why)
WHERE T.TmpTestID = 2




--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
 >> Stay informed about: Simple XML column Query Question? 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Wanted: Simple query->DOM example - Can someone point me to database neutral (i.e., works with MSAccess and SQL Server) example that populates a DOM from a SQL SELECT statement? Thanks, Siegfried

Easy query question - This one should be easy, but for some reason I can't get it. I have a table with an XML column, containing the following: <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema" DropOffCode="" PickUpCode=&quo...

How to return the difference between simple xml on SQL Ser.. - I have two simple xml as: <row> <a>1</a> <b>3</b> <c>5</c> </row> <row> <a>2</a> <b>3</b> <c>7</c> </row> What statement can retu...

Can't see Element in the Output Column - Hi , I have a peculier problem. I am having a requirement to Load XML file into database in the normalized form. When I am using following XSD for loading data. In the SSIS XML Source Editor -> Columns, I am unable to see folloing columns. These..

xml column(s) and performance - What is better to have in a table from performance point of view: (a) all data combined in single xml column (b) several xml coulmns where data is spread logically Thank you, Volodia
   Database Help (Home) -> XML All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]