Decode XML Data Type
in [Server Configuration]
|
Prev: return null for the column column_default in the view sys.informat
Next: Primary records with dependent as columns
From: Charles on 3 Aug 2010 18:45 I have something like the following: declare @msg xml set @msg = '<Message><SubItem>Some Text</SubItem></Message>' I'd like to be able to extract the value of the SubItem node, but I can't find the syntax. It might be something like (I made this up): SELECT x.SubItem FROM @msg and it would return the single row SubItem ---------------- Some Text Can anyone tell me what I should write? TIA Charles
From: Dan Guzman on 3 Aug 2010 21:17 > declare @msg xml > > set @msg = '<Message><SubItem>Some Text</SubItem></Message>' > > I'd like to be able to extract the value of the SubItem node, but I can't > find the syntax. It might be something like (I made this up): > > SELECT x.SubItem > FROM @msg > > and it would return the single row > > SubItem > ---------------- > Some Text One method: DECLARE @msg xml; SET @msg = '<Message><SubItem>Some Text</SubItem></Message>'; SELECT @msg.query('/Message/SubItem[1]').value('.', 'varchar(100)') AS SubItem; -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/
From: Charles on 4 Aug 2010 05:03 Hi Dan Thanks very much for the reply. That's exactly what I needed. I also found that I can omit the [1] and it still works, perhaps because I only have one element called SubItem? Cheers Charles "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:AFACC475-6D5D-4FDC-8E63-7C0CE1B281CA(a)microsoft.com... >> declare @msg xml >> >> set @msg = '<Message><SubItem>Some Text</SubItem></Message>' >> >> I'd like to be able to extract the value of the SubItem node, but I can't >> find the syntax. It might be something like (I made this up): >> >> SELECT x.SubItem >> FROM @msg >> >> and it would return the single row >> >> SubItem >> ---------------- >> Some Text > > One method: > > DECLARE @msg xml; > SET @msg = '<Message><SubItem>Some Text</SubItem></Message>'; > SELECT @msg.query('/Message/SubItem[1]').value('.', 'varchar(100)') AS > SubItem; > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > >
From: Dan Guzman on 4 Aug 2010 08:12 > I also found that I can omit the [1] and it still works, perhaps because I > only have one element called SubItem? Only the first SubItem will be returned due to the ordinal specification. If you omit the ordinal and have multiple SubItem elements, the text of all of the SubItem elements will be concatenated. The example below uses the XML nodes method as an alternative. This will shred SubItem elements individually. You can also use this as alternative to the XML query method I posted originally even if you have only one SubItem. DECLARE @msg xml; SET @msg = '<Message><SubItem>Some Text1</SubItem><SubItem>Some Text2</SubItem></Message>'; SELECT SubItem.query('./text()') FROM @msg.nodes('/Message/SubItem') AS Message(SubItem); -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/
From: Charles on 4 Aug 2010 08:59
That's very helpful, thanks Dan. I'll have an experiment with the two methods and decide which is going to work best in my situation. Cheers Charles "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:F5E6EC2F-F18A-4C1F-8218-7A7391485EE8(a)microsoft.com... > >> I also found that I can omit the [1] and it still works, perhaps because >> I only have one element called SubItem? > > Only the first SubItem will be returned due to the ordinal specification. > If you omit the ordinal and have multiple SubItem elements, the text of > all of the SubItem elements will be concatenated. The example below uses > the XML nodes method as an alternative. This will shred SubItem elements > individually. You can also use this as alternative to the XML query > method I posted originally even if you have only one SubItem. > > DECLARE @msg xml; > SET @msg = '<Message><SubItem>Some Text1</SubItem><SubItem>Some > Text2</SubItem></Message>'; > SELECT SubItem.query('./text()') > FROM @msg.nodes('/Message/SubItem') AS Message(SubItem); > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > > |