Xquery on XML column

Question

Post reply

Xquery on XML column

Rob Rowlands

SSC Veteran

Points: 226
More actions
May 20, 2010 at 8:51 am

#103943

Hi,
I have an xml column that has the following xml data in it:
<Root>
<Property Guid="1919C45D-CE1E-2F56-DA19-C1F19FD70F19">3790</Property>
<Property Guid="BF125622-640E-901C-7C53-A32391FBA2A5">Service Pack 2</Property>
<Property Guid="AFB4F9E6-BF48-1737-76AD-C9B3EC325B97">Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition</Property>
<Property Guid="4B8F45D0-BFFC-C96A-1811-6071AACFEDEE">03/22/2008 23:39:05</Property>
<Property Guid="6521CE39-FAEC-1489-B49C-3D7CC441B717">4</Property>
<Property Guid="A90B3983-32EA-70D8-D41C-0F5A3957639A">5.2.3790</Property>
<Property Guid="0D53D0CB-B62A-7177-E9EF-3543F94784FD">Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition</Property>
<Property Guid="D2012547-6EEF-FFCF-EF83-316AEFF4F079">16774820</Property>
<Property Guid="CBBA965A-3E95-6C6E-6F40-BDEA5D061DF9">ServerNT</Property>
<Property Guid="38242AED-43B8-1AE2-8AEC-7DBB5A6EBAB0">2.0</Property>
<Property Guid="EE4B8224-D7D5-DC1E-2413-415181A26822">C:</Property>
<Property Guid="58CB8748-E8EF-44D7-7355-7B3DA7FD686B">C:\WINDOWS</Property>
</Root>
I just don't know how to query it. If for example, I wanted to return the date information in a select statement, how would I do it? I've been trying to use xquery, but I can't get it to work.
Thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

SELECT

Y.value('@Guid[1]','char(37)') AS Guid_,

Y.value('(.)[1]','varchar(100)') AS Property_

FROM @XML.nodes('Root/Property') X(Y)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Rob Rowlands SSC Veteran Points: 226 More actions · Answer 2

Yes, that's the stuff. Thanks for that.

You couldn't recommend a good beginners guide for xquery could you?

Thanks,

Rob

LutzM SSC Guru Points: 107049 More actions · Answer 3

Sorry for answering late...

A great resource is Jacob Sebastian's blog.

And, if you don't find the answer there: post your question here on SSC. I've learned a lot and I'm still learning.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

prasad 48163 SSC Journeyman Points: 99 More actions · Answer 4

I have an XML Column 'ReturnData' and an identity column 'ReturnId' in a table called 'ReturnXML' and I need to get a rowset with columns {EmployerOrDeductorOrCollecterName (varchar), AmtPaid (int), DatePayCred(DateTime), TotTDSOnAmtPaid(int), ClaimOutOfTotTDSOnAmtPaid(int)} from the ReturnData field for a specific record in the ReturnsXML Table. The XML Data is given in this file. Can someone help me by giving the TSql Script?

LutzM SSC Guru Points: 107049 More actions · Answer 5

I took your sample file and removed a few lines to demonstrate the concept and to reduce the reply post. Both, XML NAMESPACES and CROSS APPLY are described in more detail in Jacob's blog I posted before.

DECLARE @xml XML

SET @xml='<?xml version="1.0" ?>

<ITRETURN:ITR

xmlns="http://incometaxindiaefiling.gov.in/main"

xmlns:ITR4FORM="http://incometaxindiaefiling.gov.in/ITR4"

xmlns:ITRETURN="http://incometaxindiaefiling.gov.in/main"

xmlns:ITRForm="http://incometaxindiaefiling.gov.in/master"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://incometaxindiaefiling.gov.in/main ITRMain.xsd">

<ITR4FORM:ITR4>

<ITRForm:CreationInfo>

<ITRForm:SWVersionNo>90.2</ITRForm:SWVersionNo>

</ITRForm:CreationInfo>

<ITRForm:ScheduleTDS2>

<ITRForm:TDSonOthThanSal>

<ITRForm:EmployerOrDeductorOrCollectDetl>

<ITRForm:TAN>CHEA09800A</ITRForm:TAN>

<ITRForm:EmployerOrDeductorOrCollecterName>ARS METALS P LTD</ITRForm:EmployerOrDeductorOrCollecterName>

<ITRForm:AddressDetail>

<ITRForm:AddrDetail>D-108ANNANAGAREAST CHENNAI</ITRForm:AddrDetail>

</ITRForm:AddressDetail>

</ITRForm:EmployerOrDeductorOrCollectDetl>

<ITRForm:AmtPaid>9551</ITRForm:AmtPaid>

<ITRForm:DatePayCred>2008-04-30</ITRForm:DatePayCred>

<ITRForm:TotTDSOnAmtPaid>216</ITRForm:TotTDSOnAmtPaid>

<ITRForm:ClaimOutOfTotTDSOnAmtPaid>216</ITRForm:ClaimOutOfTotTDSOnAmtPaid>

</ITRForm:TDSonOthThanSal>

<ITRForm:TDSonOthThanSal>

<ITRForm:EmployerOrDeductorOrCollectDetl>

<ITRForm:TAN>CHEA09800A</ITRForm:TAN>

<ITRForm:EmployerOrDeductorOrCollecterName>ARS METALS P LTD</ITRForm:EmployerOrDeductorOrCollecterName>

<ITRForm:AddressDetail>

<ITRForm:AddrDetail>D-108ANNANAGAREAST CHENNAI</ITRForm:AddrDetail>

</ITRForm:AddressDetail>

</ITRForm:EmployerOrDeductorOrCollectDetl>

<ITRForm:AmtPaid>8500</ITRForm:AmtPaid>

<ITRForm:DatePayCred>2008-09-29</ITRForm:DatePayCred>

<ITRForm:TotTDSOnAmtPaid>193</ITRForm:TotTDSOnAmtPaid>

<ITRForm:ClaimOutOfTotTDSOnAmtPaid>193</ITRForm:ClaimOutOfTotTDSOnAmtPaid>

</ITRForm:TDSonOthThanSal>

</ITRForm:ScheduleTDS2>

</ITR4FORM:ITR4>

</ITRETURN:ITR>

'

DECLARE @tbl TABLE

(

id INT IDENTITY(1,1),

DATA XML

)

INSERT INTO @tbl SELECT @xml

;

WITH xmlnamespaces

(

'http://incometaxindiaefiling.gov.in/ITR4' AS ITR4FORM,

'http://incometaxindiaefiling.gov.in/main' AS ITRETURN,

'http://incometaxindiaefiling.gov.in/master' AS ITRForm,

'http://www.w3.org/2001/XMLSchema-instance' AS xsi

)

SELECT

v.value('ITRForm:EmployerOrDeductorOrCollecterName[1]','varchar(30)') AS EmployerOrDeductorOrCollecterName,

c.value('ITRForm:AmtPaid[1]','int') AS AmtPaid,

c.value('ITRForm:DatePayCred[1]','datetime') AS DatePayCred,

c.value('ITRForm:TotTDSOnAmtPaid[1]','int') AS TotTDSOnAmtPaid,

c.value('ITRForm:ClaimOutOfTotTDSOnAmtPaid[1]','int') AS ClaimOutOfTotTDSOnAmtPaid

FROM @tbl tbl

CROSS APPLY

tbl.data.nodes('ITRETURN:ITR/ITR4FORM:ITR4/ITRForm:ScheduleTDS2/ITRForm:TDSonOthThanSal') T(c)

CROSS APPLY

c.nodes('ITRForm:EmployerOrDeductorOrCollectDetl') U(v)

/* result set:

EmployerOrDeductorOrCollecterNameAmtPaidDatePayCredTotTDSOnAmtPaidClaimOutOfTotTDSOnAmtPaid

ARS METALS P LTD95512008-04-30 00:00:00.000216216

ARS METALS P LTD85002008-09-29 00:00:00.000193193

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

prasad 48163 SSC Journeyman Points: 99 More actions · Answer 6

Thanks a lot. However, I solved the problem with the following code: (ReturnsXML is my Table Name and ReturnData is the XML column)

ALTER PROCEDURE [dbo].[GetTDSeDetailsForNonCorpId]

(

@id int

)

AS

DECLARE @x xml;

SET @x = (

SELECT

ReturnData.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; //b:ScheduleTDS2')

FROM ReturnsXML

WHERE ReturnId=(SELECT XmlId from ReturnsList WHERE Id=@id)

);

SELECT

Y.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; ./b:EmployerOrDeductorOrCollectDetl/b:EmployerOrDeductorOrCollecterName/text()') as Name,

Y.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; ./b:EmployerOrDeductorOrCollectDetl/b:TAN/text()') as TAN,

Y.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; ./b:AmtPaid/text()') AS AmountPaid,

Y.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; ./b:DatePayCred/text()') AS DatePaid,

Y.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; ./b:TotTDSOnAmtPaid/text()') AS TDSAmount,

Y.query('declare namespace b="http://incometaxindiaefiling.gov.in/master"; ./b:ClaimOutOfTotTDSOnAmtPaid/text()') AS AmountClaimed

FROM

@x.nodes('declare namespace b="http://incometaxindiaefiling.gov.in/master";//b:TDSonOthThanSal') X(Y)

RETURN

Prasad