Hi,
I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it.
I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)
The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns)
For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal.
<ProductMergeAudit>
<before>
<table name="table1" description="Test Desc">
<product id="OldVal">
</table>
</before>
<after>
<table name="table1" description="Test Desc">
<product id="NewVal">
</table>
</after>
</ProductMergeAudit>
Cast your TEXT to an XML datatype field and use:
SET
@.XML=CONVERT(XML,'<ProductMergeAudit><before>
<table name="table1" description="Test Desc">
<product id="OldVal"/>
</table>
</before>
<after>
<table name="table1" description="Test Desc">
<product id="NewVal"/>
</table>
</after>
</ProductMergeAudit>')
SELECT @.XML.value('(/ProductMergeAudit/before/table/product/@.id)[1]','varchar(50)'), @.XML.value('(/ProductMergeAudit/after/table/product/@.id)[1]','varchar(50)')
gives
OldVal NewVal
N.B. The line<product id="NewVal"/> had to have a / added at the end to make it valid XML.
|||
Thanks,
That work when selecting the value from a single row into a XML variable.
