XML & SQL Server - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

XML & SQL Server

Author

Topic

prem_rajani
Starting Member

2 Posts

Posted - 2006-02-28 : 06:26:49

Hey,

Sorry if the msg is in wrong forum group. Can anyone help me out with generating data from xml format. Say I have a table which contains a column with xml data format. From that column I want value of particular element. Below is an example that I'm trying to convey.

<Page id="34" pageGroupId="6" title="Risk Management & Global" render="1" type="1" template="" division="3"><Elements><GroupElement id="243" name="MetaData" render="1"><MetaDataElement id="244" key="Description" value="tbd" render="1" /> </GroupElement> </Elements> </Page>

Now I want to retrive result tbd of element MetaDataElement. So is it possible? If yes How?

Please can anyone help me out.

Prem

BabyAnn
Starting Member

10 Posts

Posted - 2006-03-01 : 08:57:26

DECLARE
@doc VARCHAR(8000),
@XML VARCHAR(8000),
@hdoc INT,
@MetaDataElement varchar(500),
@value varchar(500)

Set @XML = '<MetaDataElement id="244" key="Description" value="tbd" render="1" />'

SET @doc = '<Record>' + @XML + '</Record>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

SELECT
@MetaDataElement = MetaDataElement,
@value = value

FROM OPENXML (@hdoc,'Record/MetaDataElement') WITH (
MetaDataElement varchar(500),
value varchar(500)
)

EXEC sp_xml_removedocument @hdoc

print @MetaDataElement

print @value

Try out this......

prem_rajani
Starting Member

2 Posts

Posted - 2006-03-02 : 02:35:43

Hey,

Thnks BabyAnn I tried out in this way & it works fine. Really neat eh.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =
'<xml>
<Page id="453" pageGroupId="100" title="Viking Insurance" render="0" type="1" template="" division="5">
<Elements>
<GroupElement id="5234" name="MetaData" render="1">
<MetaDataElement id="5235" key="Description" value="Offers flexible auto insurance -- low down payments, quick issuance, easy reinstatement terms." render="1" />
<MetaDataElement id="5236" key="Keywords" value="auto, nonstandard, non-standard, specialty, Viking, 6-month, " render="1" />
<MetaDataElement id="5237" key="BizOwner" value="Greg Madson" render="1" />
<MetaDataElement id="5238" key="Subject" value="nonstandard auto product" render="1" />
<MetaDataElement id="5239" key="Audience" value="All" render="1" />
<MetaDataElement id="5240" key="CreateDate" value="02/17/2005" render="1" />
<MetaDataElement id="5241" key="UpdateDate" value="08/08/2005" render="1" />
<MetaDataElement id="5242" key="Author" value="Deb Blumer-Smith" render="1" />
</GroupElement>
</Elements>
</Page>
</xml>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * into #temp
FROM OPENXML (@idoc, '/xml/Page/Elements/GroupElement/MetaDataElement',1)
WITH ([key] varchar(10),
value varchar(20))

select * from #temp

Prem

BabyAnn
Starting Member

10 Posts

Posted - 2006-03-02 : 06:03:03

Thats Cool!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources