none
XML Value Method to return multiple records

    Question

  • I am new in XML SQl i have created a schema with namespace in SQL 2005; I can return single records with the following query using table and column name; however, i do not understand how to return multiple (nodes) records from xml segments where there are multiple (same) elements.

    Here is the query which works with one record, when trying to declare the Namespace to use nodes get error

    The schema is an HIPAA 837 schema with multiple elements ; Table name = PATIENTTRAC_SUBMISSION_HISTORY; xml field = PATIENTTRAC_EDI_SUBMISSION

    SELECT

     

    PATIENTTRAC_EDI_SUBMISSION.query('//BILLING_PAY_TO_PROVIDER')

    FROM

     

    PATIENTTRAC_SUBMISSION_HISTORY

    WHERE

     

    PATIENTTRAC_EDI_SUBMISSION.value('(//SUBSCRIBER_x0020_Last_x0020_Name)[1]', 'varchar(max)') = 'Marshall'

     

    Any help in creating the value query to return multiple records vs the [1] segment is helpful; such as how to declare namespace for the edi schema,

    "http://PITC.Schema.PatientTrackInfo"

    then using nodes against this schema

    thank you

    Thursday, October 14, 2010 5:22 PM

Answers

  • Rather than query, you do x.col.value ('(text())[1]', 'int') (replace int with the sql datatype you expect, and yes you put the datatype in single quotes).


    Russel Loski, MCT
    Thursday, October 14, 2010 8:28 PM

All replies

  • Try this

    ;
    with XMLNAMESPACES (DEFAULT 'http://PITC.Schema.PatientTrackInfo')
    
    select x.col.query('.') as outNode
    
    FROM PATIENTTRAC_SUBMISSION_HISTORY
    cross apply
    PATIENTTRAC_EDI_SUBMISSION.nodes('//BILLING_PAY_TO_PROVIDER') x(col)
    WHERE
    PATIENTTRAC_EDI_SUBMISSION.value('(//SUBSCRIBER_x0020_Last_x0020_Name)[1]', 'varchar(max)') = 'Marshall'
    
    

    Russel Loski, MCT
    Thursday, October 14, 2010 6:51 PM
  • I ran

    ;
    with XMLNAMESPACES (DEFAULT 'http://PITC.Schema.PatientTrackInfo')

    select x.col.query('.') as outNode

    FROM PATIENTTRAC_SUBMISSION_HISTORY
    cross apply
    PATIENTTRAC_EDI_SUBMISSION.nodes('//BILLING_PAY_TO_PROVIDER') x(col)

    Msg 2262, Level 16, State 1, Line 8

    XQuery [PATIENTTRAC_SUBMISSION_HISTORY.PATIENTTRAC_EDI_SUBMISSION.nodes()]: There is no element named '{http://PITC.Schema.PatientTrackInfo}:BILLING_PAY_TO_PROVIDER'

     

    left of WHERE
    PATIENTTRAC_EDI_SUBMISSION.value('(//SUBSCRIBER_x0020_Last_x0020_Name)[1]', 'varchar(max)') = 'Marshall'

    since i want to return all records for each row of the element SUBSCRIBER_x0020_Last_x0020_Name or other elements

    tried to paste actual schema but website failed, large schema

    'http://PITC.Schema.PatientTrackInfo'

     

    here is portion though

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

    xmlns:t="http://PITC.Schema.PatientTrackInfo"

    targetNamespace="http://PITC.Schema.PatientTrackInfo"><xsd:element name="PatientTrackRoot"> ....

     

     

     


    H Wayne Hayes, Jr.
    Thursday, October 14, 2010 7:07 PM
  • Try leaving off the "with XMLNAMESPACES (DEFAULT 'http://PITC.Schema.PatientTrackInfo')" statement


    Russel Loski, MCT
    Thursday, October 14, 2010 7:42 PM
  • thanks that returned the records; I also replaced to another element and it returned data, the return is the xml  vs the data, e.g.

    select

     

    x.col.query('.') as outNode

    FROM

     

    PATIENTTRAC_SUBMISSION_HISTORY

    cross

     

    apply

    PATIENTTRAC_EDI_SUBMISSION

    .nodes('//CLAIM_x0020_Patient_x0020_Account_x0020_Number') x(col)

    returns <CLAIM_x0020_Patient_x0020_Account_x0020_Number>123293</CLAIM_x0020_Patient_x0020_Account_x0020_Number>

    can i modify the query to return .value (123293)  vs the xml 

    "select x.col.query('.') as outNode"

    thank you again at least i am moving forward with this

    wayne

     


    H Wayne Hayes, Jr.
    Thursday, October 14, 2010 7:56 PM
  • Rather than query, you do x.col.value ('(text())[1]', 'int') (replace int with the sql datatype you expect, and yes you put the datatype in single quotes).


    Russel Loski, MCT
    Thursday, October 14, 2010 8:28 PM
  • thank you again

    wayne

     


    H Wayne Hayes, Jr.
    Thursday, October 14, 2010 8:32 PM
  • I ran this query

    select x.col.value ('

     

    (text())[1]', 'varchar(max)') as outNode

    FROM PATIENTTRAC_SUBMISSION_HISTORY

    cross apply

    PATIENTTRAC_EDI_SUBMISSION.nodes('

     

    //CLAIM_x0020_Patient_x0020_Account_x0020_Number') x(col)

    Msg 9312, Level 16, State 1, Line 3

    XQuery [PATIENTTRAC_SUBMISSION_HISTORY.PATIENTTRAC_EDI_SUBMISSION.value()]: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found 'element(CLAIM_x0020_Patient_x0020_Account_x0020_Number,xs:string)'.


    H Wayne Hayes, Jr.
    Thursday, October 14, 2010 8:42 PM