Need help with XML column data values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with XML column data values

Author

Topic

srujanavinnakota
Starting Member

34 Posts

Posted - 2014-01-09 : 16:53:07

Hi,
Can anybody please help me with the below xml. I need extract all the xml values like below.
AwarYear Comments FieldCode FieldNumber Key Value
AY2013-14 AAI: Adjusted Available Income AAI 306 Blank None Calculated

Here is the sample XML.
<SchemaType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/process">
<AwardYear>AY2013_14</AwardYear>
<Fields>
<FieldSchema>
<Comments>AAI: Adjusted Available Income</Comments>
<DbLocation>IsirData</DbLocation>
<FieldCode>AAI</FieldCode>
<FieldNumber>306</FieldNumber>
<ReportDisplay>Data</ReportDisplay>
<ValidContent>
<ValidValueContent xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d5p1:KeyValueOfstringstring>
<d5p1:Key>Blank</d5p1:Key>
<d5p1:Value>None calculated</d5p1:Value>
</d5p1:KeyValueOfstringstring>
</ValidValueContent>
</ValidContent>
</FieldSchema>
</Fields>
</SchemaType>

Please do the need full. Thanks in advance.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-09 : 20:41:39

Do you that you need to do like the following?

----------------------------------------------

DECLARE @xmldoc nvarchar(max) = N'
<SchemaType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/process">
<AwardYear>AY2013_14</AwardYear>
<Fields>
<FieldSchema>
<Comments>AAI: Adjusted Available Income</Comments>
<DbLocation>IsirData</DbLocation>
<FieldCode>AAI</FieldCode>
<FieldNumber>306</FieldNumber>
<ReportDisplay>Data</ReportDisplay>
<ValidContent>
<ValidValueContent xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d5p1:KeyValueOfstringstring>
<d5p1:Key>Blank</d5p1:Key>
<d5p1:Value>None calculated</d5p1:Value>
</d5p1:KeyValueOfstringstring>
</ValidValueContent>
</ValidContent>
</FieldSchema>
</Fields>
</SchemaType>';

DECLARE @hxmldoc int;

--not forget namespace definition
EXEC sp_xml_preparedocument @hxmldoc OUTPUT, @xmldoc,
N'<root
xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns:default="http://schemas.datacontract.org/process"
xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />';

--adjust types in with clause, if you need
SELECT *
FROM OPENXML(@hxmldoc, N'/default:SchemaType', 0)
WITH(
AwardYear nvarchar(100) N'default:AwardYear/text()',
Comments nvarchar(max) N'//default:Comments/text()',
FieldCode nvarchar(100) N'//default:FieldCode/text()',
FieldNumber int N'//default:FieldNumber/text()',
[Key] nvarchar(100) N'//d5p1:Key/text()',
Value nvarchar(100) N'//d5p1:Value/text()');

EXEC sp_xml_removedocument @hxmldoc;

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:21:22

if its 2005 or above better to use xml functions like nodes() etc over OPENXML


declare @x xml='<SchemaType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/process">
<AwardYear>AY2013_14</AwardYear>
<Fields>
<FieldSchema>
<Comments>AAI: Adjusted Available Income</Comments>
<DbLocation>IsirData</DbLocation>
<FieldCode>AAI</FieldCode>
<FieldNumber>306</FieldNumber>
<ReportDisplay>Data</ReportDisplay>
<ValidContent>
<ValidValueContent xmlns:d5p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d5p1:KeyValueOfstringstring>
<d5p1:Key>Blank</d5p1:Key>
<d5p1:Value>None calculated</d5p1:Value>
</d5p1:KeyValueOfstringstring>
</ValidValueContent>
</ValidContent>
</FieldSchema>
</Fields>
</SchemaType>'

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as i,'http://schemas.datacontract.org/process' as [default],'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d5p1)
SELECT t.u.value('../../default:AwardYear[1]','varchar(10)') AS AwardYr,
t.u.value('default:Comments[1]','varchar(100)') AS Comments,
t.u.value('default:FieldCode[1]','varchar(100)') AS FieldCode,
t.u.value('default:FieldNumber[1]','int') AS FieldNumber,
t.u.value('(//d5p1:Key)[1]','varchar(100)') AS [Key],
t.u.value('(//d5p1:Value)[1]','varchar(100)') AS Value
FROm @X.nodes('/default:SchemaType/default:Fields/default:FieldSchema')t(u)

output
----------------------------------------------------------------------------------------------------
AwardYr	        Comments	                        FieldCode FieldNumber   Key	          Value
-----------------------------------------------------------------------------------------------------
AY2013_14	AAI: Adjusted Available Income	        AAI       306 	      Blank	          None calculated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-10 : 07:34:16

quote:
Originally posted by visakh16

if its 2005 or above better to use xml functions like nodes() etc over OPENXML

It's cool!

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 08:18:07

quote:
Originally posted by nagino

quote:
Originally posted by visakh16

if its 2005 or above better to use xml functions like nodes() etc over OPENXML

It's cool!

-------------------------------------
From Japan
Sorry, my English ability is limited.

Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources