SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 XML & SQL Server
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

prem_rajani
Starting Member

India
2 Posts

Posted - 02/28/2006 :  06:26:49  Show Profile  Send prem_rajani a Yahoo! Message
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

India
10 Posts

Posted - 03/01/2006 :  08:57:26  Show Profile
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......
Go to Top of Page

prem_rajani
Starting Member

India
2 Posts

Posted - 03/02/2006 :  02:35:43  Show Profile  Send prem_rajani a Yahoo! Message
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
Go to Top of Page

BabyAnn
Starting Member

India
10 Posts

Posted - 03/02/2006 :  06:03:03  Show Profile
Thats Cool!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000