jeudi 13 août 2015

How to extract xml string to make a new colums in sql using XQuery/XPath?

Hi I have here my code for now and Im not getting the result correctly using this codes;

Declare @txt varchar(max) = (select audit_val from AuditTable where audit_val like '%update_dt%' and audit_val like '%PEK150700019-001%')
Declare @xml XML = CONVERT(XML,@txt)
SELECT 
    @xml.value('(/i/@guest_id)[1]', 'varchar(100)') as guest_id
    , @xml.value('(/i/@updated_by)[1]', 'varchar(100)') as updated_by
    , @xml.value('(/i/@update_dt)[1]', 'datetime') as update_dt

what I am getting is null values and it says it returned more than one value, using select top 1 will do, but I need to extract all of the string needed on that column from the table rows, can someone help me how to fix this ? thanks in advance :)

Note** heres a sample output;

=================================================================
|guest_id          |   updated_by     |     update_dt           |
|==================|==================|=========================|
|PEK150700019-001  |  Wane            |  2015-08-11T13:59:09.550|
|------------------|------------------|-------------------------|



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire