February 27, 2012 at 10:41 am
DECLARE @XML XML
SET @XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>
<Author><Name>Joseph</Name><Age>35</Age></Author></Book></Books></root>'
I have a XML like this; I would like to get the Result as same like below
Author
<Name>Joseph</Name><Age>35</Age>
Can anyone help me to get the XMl text inside the element/node Author...?
February 27, 2012 at 11:48 am
Will this suffice?
DECLARE @XML XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>
<Author><Name>Joseph</Name><Age>35</Age></Author></Book></Books></root>' ;
SELECT @XML.query('/root/Books/Book/Author') ;
If not, you'll need to query the values within the Author node, and then recompose it into XML with a FOR XML clause.
DECLARE @XML XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>
<Author><Name>Joseph</Name><Age>35</Age></Author></Book></Books></root>' ;
SELECT
@XML.value('(/root/Books/Book/Author/Name/text())[1]','varchar(100)') AS Name,
@XML.value('(/root/Books/Book/Author/Age/text())[1]','varchar(100)') AS Age
FOR XML PATH('') ;
- 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
February 27, 2012 at 7:47 pm
Actually, this is not the one i expected. i have to write a XQuery to get the XML content inside the Author element/Node
I tried this Query;
SELECT
Author = T.item.query('Author')
FROM @XML.nodes('root/Books/Book') AS T(item);
But i'm getting the answer along with the Author Node;
<Author><Name>Joseph</Name><Age>35</Age></Author>
The element inside the Author Tag is not static, it would vary.
I do not want substring to get the expected value. Please help me through XQuery
February 28, 2012 at 7:20 am
There are a couple of ways to do this.
Here are some samples. Try these, see if they get you started in the right direction.
DECLARE @XML XML = '<root><Books><Book><ID>2</ID><Name>Sql Server Admin</Name>
<Author><Name>Joseph</Name><Age>35</Age><Blah>Whatever</Blah></Author></Book></Books></root>' ;
-- Example 1
SELECT
@XML.value('(/root/Books/Book/Author/*/text())[1]','varchar(100)') AS Col1,
@XML.value('(/root/Books/Book/Author/*/text())[2]','varchar(100)') AS Col2
FOR XML PATH('') ;
-- Example 2
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Row,
x.y.value('local-name(.)', 'VARCHAR(50)') AS ColName,
x.y.value('.', 'VARCHAR(50)') AS ColValue
FROM @XML.nodes('/root/Books/Book/Author/*') x (y) ;
On the second one, you can use a dynamic pivot to turn that into named columns and rows. There's a sample of how to do a dynamic pivot here: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx. There are other examples around - Bing/Google "tsql dynamic pivot" and you'll find plenty of articles and examples around.
Example 2 will pull any number of sub-values from within the Author node and get the tag name and value for each.
- 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
February 26, 2013 at 10:31 am
Try:
select @xml.query('(//Author)[1]/*')
February 26, 2013 at 10:50 am
The best way to get the contents of an element (especially if the content may change from document to document) is to use the child axis
SELECT @XML.query('//Author/child::*') ;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply