How to return multiple values of XML child Nodes
-
I have the following example XML Data
#Atd_Order_Row_Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"##Dealer_Export_Non_Billed##Targets##Target#Fish 4 Cars#/Target##Target#V Cars#/Target##/Targets##/Dealer_Export_Non_Billed##Dealer_Export##Dealer_Stock_Export##Partner#2nd Byte - Scott Hall Feed#/Partner##Source#Horizon/Cherry Picked#/Source##Advanced_Filter_Flag#No#/Advanced_Filter_Flag##Partner_ID#58#/Partner_ID##Allow_Feed_IDs#True#/Allow_Feed_IDs##/Dealer_Stock_Export##Dealer_Stock_Export##Partner#AutoVogue#/Partner##Feed_ID#1234#/Feed_ID##Source#Dealer Web Site#/Source##Advanced_Filter_Flag#Yes#/Advanced_Filter_Flag##Partner_ID#72#/Partner_ID##Allow_Feed_IDs#True#/Allow_Feed_IDs##Advanced_Filter##Advanced_Filter_ID#c5bf4fcb-7a96-4b71-9039-265394ac260a#/Advanced_Filter_ID##Partner#AutoVogue#/Partner##Make#ALFA ROMEO#/Make##Model /##Operator /##/Advanced_Filter##Advanced_Filter##Advanced_Filter_ID#b71e24df-93d3-4759-a910-95fff02faba6#/Advanced_Filter_ID##Partner#AutoVogue#/Partner##Make#BENTLEY#/Make##Model#CONTINENTAL#/Model##Operator /##/Advanced_Filter##Advanced_Filter##Advanced_Filter_ID#015c641f-30d6-4c9a-b09f-286524683b5d#/Advanced_Filter_ID##Partner#AutoVogue#/Partner##Make /##Model /##Operator#>#/Operator##Price#5000#/Price##/Advanced_Filter##Advanced_Filter##Advanced_Filter_ID#55d846e7-f535-4f66-b2ce-6b04f84788b3#/Advanced_Filter_ID##Partner#AutoVogue#/Partner##Make /##Model /##Operator#<#/Operator##Price#15000#/Price##/Advanced_Filter##Advanced_Filter##Advanced_Filter_ID#25239ca9-7c3f-4bd9-b2f9-adfda6cc33b5#/Advanced_Filter_ID##Partner#AutoVogue#/Partner##Make#CHRYSLER#/Make##Model /##Operator#<#/Operator##Price#10000#/Price##/Advanced_Filter##Advanced_Filter##Advanced_Filter_ID#1f108e36-ba62-4fbd-ac5c-3fa91edadb0a#/Advanced_Filter_ID##Partner#AutoVogue#/Partner##Make#DAEWOO#/Make##Model#KORANDO#/Model##Operator#>#/Operator##Price#12000#/Price##/Advanced_Filter##/Dealer_Stock_Export##Dealer_Stock_Export##Partner#G Forces#/Partner##Source#Horizon/Cherry Picked#/Source##Replacement_Phone_No#016130306788#/Replacement_Phone_No##Advanced_Filter_Flag#No#/Advanced_Filter_Flag##Partner_ID#126#/Partner_ID##Allow_Feed_IDs#False#/Allow_Feed_IDs##/Dealer_Stock_Export##/Dealer_Export##/Atd_Order_Row_Data#I currently use the following XML to return the names of each dealer_stock_export/partner (I had to replace left and right node arrowswith # to make it display in this post)
SELECT Order_ID,Order_Row_ID,A.node.value('(.)[1]', 'varchar(50)') AS TargetFROM order_row_misc_data CROSS APPLY Data_xml.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export/Partner') AS A(node)WHERE Misc_Data_ID = 6000002and this returns the values
2nd Byte - Scott Hall Feed
AutoVogue
G Forces
What I now need to do is to return the values for dealer_stock_export/Feed_ID for each of these rows so that I end up with
2nd Byte - Scott Hall Feed Null
AutoVogue 1234
G Forces Null
I have tried
SELECT Order_ID,Order_Row_ID,A.node.value('(.)[1]', 'varchar(50)') AS Target,B.node.value('(.)[1]', 'varchar(50)') AS feed_idFROM order_row_misc_dataCROSS APPLY Data_xml.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export/Partner') AS A(node)OUTER APPLY Data_xml.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export/Feed_ID') AS B(node)WHERE Misc_Data_ID = 6000002But this is not correct as it returns
2nd Byte - Scott Hall Feed 1234
AutoVogue 1234
G Forces 1234
Can anyone help
Thanks in advance
-
I don't have time to turn that back into valid XML right now, but if you could post the XML in a text file attached to a post, I can probably figure out how to query what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks for looking at this, I've attached the file as requested.
I should also add that this is a sample of XML stored as XML data type within a table
CREATE TABLE [dbo].[Order_Row_Data](
[Order_ID] [uniqueidentifier] NOT NULL,
[Order_Row_ID] [dbo].[dt_integer_key] NOT NULL,
[Misc_Data_ID] [dbo].[dt_integer_key] NOT NULL,
[Data_boolean] [bit] NULL,
[Data_date] [dbo].[dt_date_time] NULL,
[Data_integer] [int] NULL,
[Data_float] [float] NULL,
[Data_string] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
[Data_text] [text] COLLATE Latin1_General_CI_AS NULL,
[Data_xml] [xml] NULL,
[Cell_colour] [tinyint] NOT NULL,
CONSTRAINT [pk_Order_Row_Misc_Data] PRIMARY KEY CLUSTERED
The SQL is to be used to create view over this table which can then be used in reports with the fields
Order_Id, Order_Row_ID, Targets, Feed_ID
HTH
-
Here's what I did:
declare @XML XMLselect @XML = '(Your XML String)'SELECT A.node.value('(Partner)[1]', 'varchar(50)') AS Target,A.node.value('(Feed_ID)[1]', 'varchar(50)')FROM @XML.nodes('/Atd_Order_Row_Data/Dealer_Export/Dealer_Stock_Export') AS A(node)That got the result you need.
The change is taking the last bit off the nodes XQuery string and having both value functions run off of that.
I can't precisely re-create your environment, because you have user-defined data types in your table definition, and I don't have sample data for the other columns, but this part should give you enough to go off of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Of course !! it make sense now
thanks GSquared - saved me a lot of head scratching
-
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply