Home Forums Programming XML XML.Value when it has attributes? RE: XML.Value when it has attributes?

  • thanks, arthur, but that's kind of what's confusing me;

    if i don't use the namespace, i get the desired results for the second item but not ht efirst; if i use the name space, i get the first item, but not the second

    is it because, once you use a namespace, if it's not mentioned it's malformed?

    --now start shredding the xml

    WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

    SELECT *,

    RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/sp:Message/sp:Body/sp:RefillRequest/sp:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

    FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

    )MyAlias

    --now start shredding the xml

    WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)

    SELECT *,

    RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/Message/Body/RefillRequest/RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

    FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

    )MyAlias

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!