Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get the inner XML from an xml element using XQuery Expand / Collapse
Author
Message
Posted Monday, February 27, 2012 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 01, 2013 12:41 PM
Points: 17, Visits: 61
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...?
Post #1258399
Posted Monday, February 27, 2012 11:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1258440
Posted Monday, February 27, 2012 7:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 01, 2013 12:41 PM
Points: 17, Visits: 61
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
Post #1258590
Posted Tuesday, February 28, 2012 7:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1258873
Posted Tuesday, February 26, 2013 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 10:29 AM
Points: 1, Visits: 3
Try:

select @xml.query('(//Author)[1]/*')
Post #1424176
Posted Tuesday, February 26, 2013 10:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:28 PM
Points: 320, Visits: 211
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::*') ;




Post #1424185
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse