Quering an XML template sql server table

Wednesday, June 20, 2012 3:35 PM

Dear All,

<?xml version="1.0" encoding="utf-8"?>
<SCREEN NAME="restictedmedreq" TITLE="Restricted Medicines Request" RWOBJECT="RioSGenData2.GenericScreen" INDEXTYPE="LIST" HISTTYPE="G" HISTMAX="1" HISTDIR="D" PASSDATA="N" XVAL="N" XNAV="Y">
      <DEFINITION>
            <FIELD NAME="type12_UpdatedBy" TYPE="SEARCH" HORDER="10" VORDER="9997">
                  <PROMPT>Updated by</PROMPT>
                  <SEARCH LIBID="SGPerson"/>
            </FIELD>
            <FIELD NAME="type12_UpdatedDate" TYPE="DATETIME" HORDER="10" VORDER="9998">
                  <PROMPT>Updated on</PROMPT>
            </FIELD>
            <FIELD NAME="system_ValidationData" TYPE="HIDDEN" XTYPE="text" HORDER="10" VORDER="9996" XSQLTYPE="NTEXT">
                  <PROMPT></PROMPT>
            </FIELD>
            <FIELD NAME="ClientID" TYPE="CLIENTID" HORDER="10" VORDER="1" MANDATORY="Y" XTYPE="clientid" XSQLTYPE="ClientIDType" XNODEL="Y" INDEX="Y">
                  <PROMPT>
                        <span>
                              <PROMPT>Service user</PROMPT>
                        </span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="90" NAME="SB6aBipoler" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
                  <PROMPT>
                        <span>Bipolar affective disorder</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="100" NAME="SB6bDepression" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
                  <PROMPT>
                        <span>Depression</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="110" NAME="SB6cSchizo" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
                  <PROMPT>
                        <span>Schizophrenia</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="120" NAME="SC1Clozapine" XTYPE="table" MANDATORY="N" RFC="N" TYPE="SELECT" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
                  <PROMPT>
                        <span>If diagnosis is Schizophrenia and 2 or more anti psychotics have been tried has Clozapine been considered?</span>
                  </PROMPT>
                  <SELECT TABLE="UserMasterYesYesNoNA" COLS="Code,CodeDescription" CONDITIONS="Deleted = 0"/>
            </FIELD>
            <FIELD HORDER="10" VORDER="140" NAME="SC3AuthMed" XTYPE="ynradio" MANDATORY="N" RFC="N" TYPE="YNRADIO" XSQLTYPE="tinyint">
                  <PROMPT>
                        <span>Has the patient’s current consultant authorised the request for medication?</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="150" NAME="SD1Agree" XTYPE="ynradio" MANDATORY="N" RFC="N" TYPE="YNRADIO" XSQLTYPE="tinyint">
                  <PROMPT>
                        <span>Has agreement been received from long-term/community consultant?</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="170" NAME="SD3Date" XTYPE="date" MANDATORY="N" RFC="N" TYPE="DATE" XSQLTYPE="datetime">
                  <PROMPT>
                        <span>Date of agreement </span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="180" NAME="SD4AgreeRcd" XTYPE="ynradio" MANDATORY="N" RFC="N" TYPE="YNRADIO" XSQLTYPE="tinyint">
                  <PROMPT>
                        <span>Record of agreement has been included in the care record: </span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="50" NAME="SB1Ref" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
                  <SEARCH TABLE="" LIBID="Ref-Open and Closed Less Than 30"/>
                  <PROMPT>
                        <span>Please select referral</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="55" NAME="con" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
                  <SEARCH TABLE="" LIBID="ConsultantV2"/>
                  <PROMPT>
                        <span>Current Prescribing Consultant</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="60" NAME="ICD10" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
                  <PROMPT>
                        <span>ICD10 Diagnosis</span>
                  </PROMPT>
                  <SEARCH TABLE="" LIBID="Client Diagnosis"/>
            </FIELD>
            <FIELD HORDER="10" VORDER="80" NAME="Reason" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
                  <PROMPT>
                        <span>Reason for use</span>
                  </PROMPT>
            </FIELD>
            <HEADING HORDER="10" VORDER="85" NAME="head1" XTYPE="heading">
                  <span>BSMHFT Prescribing Guidelines being applied:</span>
            </HEADING>
            <FIELD HORDER="10" VORDER="130" NAME="improve" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
                  <PROMPT>
                        <span>How will improvement be measured?</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="200" NAME="comment2" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
                  <PROMPT>
                        <span>Details</span>
                  </PROMPT>
            </FIELD>
            <COMMENT HORDER="10" VORDER="20" NAME="com1" XTYPE="comment2" DEFAULT="show">
                  <span>
                        The medicines this guidance….</span>
            </COMMENT>
            <COMMENT HORDER="10" VORDER="19" NAME="com0" XTYPE="comment2" DEFAULT="show">
                  <span>
                        <b>Note: to print this form fill in and save here then print via Editable Letters</b>
                  </span>
            </COMMENT>
            <HEADING HORDER="10" VORDER="25" NAME="SectionA" XTYPE="heading">
                  <span>
                        <u>Section A</u>
                  </span>
            </HEADING>
            <HEADING HORDER="10" VORDER="45" NAME="SectionB" XTYPE="heading">
                  <span>
                        <u>Section B</u>
                  </span>
            </HEADING>
            <HEADING HORDER="10" VORDER="115" NAME="SectionC" XTYPE="heading">
                  <span>
                        <u>Section C</u>
                  </span>
            </HEADING>
            <HEADING HORDER="10" VORDER="145" NAME="SectionD" XTYPE="heading">
                  <span>
                        <u>Section D</u>
                  </span>
            </HEADING>
            <FIELD NAME="AssessmentDate" TYPE="DATETIME" HORDER="10" VORDER="2" MANDATORY="Y" XTYPE="datetime" XSQLTYPE="DATETIME" XNODEL="Y" INDEX="Y">
                  <PROMPT>
                        <span>
                              <PROMPT>Request Date</PROMPT>
                        </span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="30" NAME="presc" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
                  <PROMPT>
                        <span>New prescription or only previously received on PRN basis (complete all sections as required)</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="40" NAME="SA2AE" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
                  <PROMPT>
                        <span>Already established (complete section B only)</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="70" NAME="SB4Med" XTYPE="table" MANDATORY="Y" RFC="N" TYPE="SELECT" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
                  <SELECT TABLE="UserMasterRestrictedMeds" COLS="Code,CodeDescription" CONDITIONS="Deleted = 0"/>
                  <PROMPT>
                        <span>Medicine to be used</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="117" NAME="SC0POT" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
                  <PROMPT>
                        <span>Previous options tried</span>
                  </PROMPT>
            </FIELD>
            <FIELD HORDER="10" VORDER="155" NAME="SD2LTC" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
                  <PROMPT>
                        <span>Name of long-term/community consultant:</span>
                  </PROMPT>
                  <SEARCH TABLE="" LIBID="ConsultantV2"/>
            </FIELD>
      </DEFINITION>
      <INDICES>
            <INDEX NAME="KEY" MASTER="1" DATEBINDCOL="1">
                  <COL ORDER="0" XNODEL="Y">ClientID</COL>
                  <COL ORDER="1" XNODEL="Y">AssessmentDate</COL>
                  <COL ORDER="2">type12_OriginalNoteID</COL>
                  <COL ORDER="3">type12_NoteID</COL>
            </INDEX>
      </INDICES>
      <BUTTONBAR>
            <MODE ID="0">
                  <BUTTON ORDER="0" COMMAND="0">First</BUTTON>
                  <BUTTON ORDER="1" COMMAND="1">Previous</BUTTON>
                  <BUTTON ORDER="8" COMMAND="2">Next</BUTTON>
                  <BUTTON ORDER="9" COMMAND="3">Last</BUTTON>
                  <BUTTON ORDER="2" COMMAND="6">Edit current</BUTTON>
                  <BUTTON ORDER="3" COMMAND="7">Create new</BUTTON>
                  <BUTTON ORDER="7" COMMAND="4">History</BUTTON>
                  <BUTTON ORDER="5" COMMAND="13">Index</BUTTON>
            </MODE>
            <MODE ID="1">
                  <BUTTON ORDER="1" COMMAND="9">Save</BUTTON>
                  <BUTTON ORDER="3" COMMAND="8">Cancel</BUTTON>
                  <BUTTON ORDER="2" COMMAND="14">Clear</BUTTON>
            </MODE>
            <MODE ID="2">
                  <BUTTON ORDER="1" COMMAND="10">Save</BUTTON>
                  <BUTTON ORDER="3" COMMAND="8">Cancel</BUTTON>
                  <BUTTON ORDER="2" COMMAND="14">Clear</BUTTON>
            </MODE>
            <MODE ID="3">
                  <BUTTON ORDER="1" COMMAND="5">Return</BUTTON>
            </MODE>
      </BUTTONBAR>
</SCREEN>

Thank you

Aku

I have a table called GenScreenXML with a field name ScreenXML which contains an XML temples like the one below. I need to pull all the table attributes beginning the "UserMaster%" for all the records. Each template can contain any number of UserMaster tables at any position. Can anyone help me?

Aku

All Replies

Wednesday, June 20, 2012 4:36 PM

Answerer

Here's a couple of examples:

DECLARE @GenScreenXML  TABLE ( yourRowId INT IDENTITY, ScreenXML XML )

INSERT INTO @GenScreenXML ( ScreenXML )
SELECT '<SCREEN NAME="restictedmedreq" TITLE="Restricted Medicines Request" RWOBJECT="RioSGenData2.GenericScreen" INDEXTYPE="LIST" HISTTYPE="G" HISTMAX="1" HISTDIR="D" PASSDATA="N" XVAL="N" XNAV="Y">
  <DEFINITION>
    <FIELD NAME="type12_UpdatedBy" TYPE="SEARCH" HORDER="10" VORDER="9997">
      <PROMPT>Updated by</PROMPT>
      <SEARCH LIBID="SGPerson" />
    </FIELD>
    <FIELD NAME="type12_UpdatedDate" TYPE="DATETIME" HORDER="10" VORDER="9998">
      <PROMPT>Updated on</PROMPT>
    </FIELD>
    <FIELD NAME="system_ValidationData" TYPE="HIDDEN" XTYPE="text" HORDER="10" VORDER="9996" XSQLTYPE="NTEXT">
      <PROMPT />
    </FIELD>
    <FIELD NAME="ClientID" TYPE="CLIENTID" HORDER="10" VORDER="1" MANDATORY="Y" XTYPE="clientid" XSQLTYPE="ClientIDType" XNODEL="Y" INDEX="Y">
      <PROMPT>
        <span>
          <PROMPT>Service user</PROMPT>
        </span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="90" NAME="SB6aBipoler" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
      <PROMPT>
        <span>Bipolar affective disorder</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="100" NAME="SB6bDepression" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
      <PROMPT>
        <span>Depression</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="110" NAME="SB6cSchizo" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
      <PROMPT>
        <span>Schizophrenia</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="120" NAME="SC1Clozapine" XTYPE="table" MANDATORY="N" RFC="N" TYPE="SELECT" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
      <PROMPT>
        <span>If diagnosis is Schizophrenia and 2 or more anti psychotics have been tried has Clozapine been considered?</span>
      </PROMPT>
      <SELECT TABLE="UserMasterYesYesNoNA" COLS="Code,CodeDescription" CONDITIONS="Deleted = 0" />
    </FIELD>
    <FIELD HORDER="10" VORDER="140" NAME="SC3AuthMed" XTYPE="ynradio" MANDATORY="N" RFC="N" TYPE="YNRADIO" XSQLTYPE="tinyint">
      <PROMPT>
        <span>Has the patient’s current consultant authorised the request for medication?</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="150" NAME="SD1Agree" XTYPE="ynradio" MANDATORY="N" RFC="N" TYPE="YNRADIO" XSQLTYPE="tinyint">
      <PROMPT>
        <span>Has agreement been received from long-term/community consultant?</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="170" NAME="SD3Date" XTYPE="date" MANDATORY="N" RFC="N" TYPE="DATE" XSQLTYPE="datetime">
      <PROMPT>
        <span>Date of agreement </span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="180" NAME="SD4AgreeRcd" XTYPE="ynradio" MANDATORY="N" RFC="N" TYPE="YNRADIO" XSQLTYPE="tinyint">
      <PROMPT>
        <span>Record of agreement has been included in the care record: </span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="50" NAME="SB1Ref" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
      <SEARCH TABLE="" LIBID="Ref-Open and Closed Less Than 30" />
      <PROMPT>
        <span>Please select referral</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="55" NAME="con" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
      <SEARCH TABLE="" LIBID="ConsultantV2" />
      <PROMPT>
        <span>Current Prescribing Consultant</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="60" NAME="ICD10" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
      <PROMPT>
        <span>ICD10 Diagnosis</span>
      </PROMPT>
      <SEARCH TABLE="" LIBID="Client Diagnosis" />
    </FIELD>
    <FIELD HORDER="10" VORDER="80" NAME="Reason" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
      <PROMPT>
        <span>Reason for use</span>
      </PROMPT>
    </FIELD>
    <HEADING HORDER="10" VORDER="85" NAME="head1" XTYPE="heading">
      <span>BSMHFT Prescribing Guidelines being applied:</span>
    </HEADING>
    <FIELD HORDER="10" VORDER="130" NAME="improve" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
      <PROMPT>
        <span>How will improvement be measured?</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="200" NAME="comment2" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
      <PROMPT>
        <span>Details</span>
      </PROMPT>
    </FIELD>
    <COMMENT HORDER="10" VORDER="20" NAME="com1" XTYPE="comment2" DEFAULT="show">
      <span>
                        The medicines this guidance….</span>
    </COMMENT>
    <COMMENT HORDER="10" VORDER="19" NAME="com0" XTYPE="comment2" DEFAULT="show">
      <span>
        <b>Note: to print this form fill in and save here then print via Editable Letters</b>
      </span>
    </COMMENT>
    <HEADING HORDER="10" VORDER="25" NAME="SectionA" XTYPE="heading">
      <span>
        <u>Section A</u>
      </span>
    </HEADING>
    <HEADING HORDER="10" VORDER="45" NAME="SectionB" XTYPE="heading">
      <span>
        <u>Section B</u>
      </span>
    </HEADING>
    <HEADING HORDER="10" VORDER="115" NAME="SectionC" XTYPE="heading">
      <span>
        <u>Section C</u>
      </span>
    </HEADING>
    <HEADING HORDER="10" VORDER="145" NAME="SectionD" XTYPE="heading">
      <span>
        <u>Section D</u>
      </span>
    </HEADING>
    <FIELD NAME="AssessmentDate" TYPE="DATETIME" HORDER="10" VORDER="2" MANDATORY="Y" XTYPE="datetime" XSQLTYPE="DATETIME" XNODEL="Y" INDEX="Y">
      <PROMPT>
        <span>
          <PROMPT>Request Date</PROMPT>
        </span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="30" NAME="presc" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
      <PROMPT>
        <span>New prescription or only previously received on PRN basis (complete all sections as required)</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="40" NAME="SA2AE" XTYPE="checkbox" MANDATORY="N" RFC="N" TYPE="CHECKBOX" XSQLTYPE="bit">
      <PROMPT>
        <span>Already established (complete section B only)</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="70" NAME="SB4Med" XTYPE="table" MANDATORY="Y" RFC="N" TYPE="SELECT" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
      <SELECT TABLE="UserMasterRestrictedMeds" COLS="Code,CodeDescription" CONDITIONS="Deleted = 0" />
      <PROMPT>
        <span>Medicine to be used</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="117" NAME="SC0POT" XTYPE="wp" MANDATORY="N" RFC="N" TYPE="WP" XSQLTYPE="ntext" DISPCOLS="81" DISPROWS="3" DATALEN="5000" XTRACONFIG="Y">
      <PROMPT>
        <span>Previous options tried</span>
      </PROMPT>
    </FIELD>
    <FIELD HORDER="10" VORDER="155" NAME="SD2LTC" XTYPE="search" MANDATORY="N" RFC="N" TYPE="SEARCH" XSQLTYPE="nvarchar(20)" XTRACONFIG="Y">
      <PROMPT>
        <span>Name of long-term/community consultant:</span>
      </PROMPT>
      <SEARCH TABLE="" LIBID="ConsultantV2" />
    </FIELD>
  </DEFINITION>
  <INDICES>
    <INDEX NAME="KEY" MASTER="1" DATEBINDCOL="1">
      <COL ORDER="0" XNODEL="Y">ClientID</COL>
      <COL ORDER="1" XNODEL="Y">AssessmentDate</COL>
      <COL ORDER="2">type12_OriginalNoteID</COL>
      <COL ORDER="3">type12_NoteID</COL>
    </INDEX>
  </INDICES>
  <BUTTONBAR>
    <MODE ID="0">
      <BUTTON ORDER="0" COMMAND="0">First</BUTTON>
      <BUTTON ORDER="1" COMMAND="1">Previous</BUTTON>
      <BUTTON ORDER="8" COMMAND="2">Next</BUTTON>
      <BUTTON ORDER="9" COMMAND="3">Last</BUTTON>
      <BUTTON ORDER="2" COMMAND="6">Edit current</BUTTON>
      <BUTTON ORDER="3" COMMAND="7">Create new</BUTTON>
      <BUTTON ORDER="7" COMMAND="4">History</BUTTON>
      <BUTTON ORDER="5" COMMAND="13">Index</BUTTON>
    </MODE>
    <MODE ID="1">
      <BUTTON ORDER="1" COMMAND="9">Save</BUTTON>
      <BUTTON ORDER="3" COMMAND="8">Cancel</BUTTON>
      <BUTTON ORDER="2" COMMAND="14">Clear</BUTTON>
    </MODE>
    <MODE ID="2">
      <BUTTON ORDER="1" COMMAND="10">Save</BUTTON>
      <BUTTON ORDER="3" COMMAND="8">Cancel</BUTTON>
      <BUTTON ORDER="2" COMMAND="14">Clear</BUTTON>
    </MODE>
    <MODE ID="3">
      <BUTTON ORDER="1" COMMAND="5">Return</BUTTON>
    </MODE>
  </BUTTONBAR>
</SCREEN>'


SELECT 
	x.y.query('.'),
	x.y.value('@TABLE', 'VARCHAR(MAX)') [table],
	x.y.value('@COLS', 'VARCHAR(MAX)') cols,
	x.y.value('@CONDITIONS', 'VARCHAR(MAX)') conditions
FROM @GenScreenXML t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]') x(y)


SELECT 
	x.y.value('local-name(.)', 'VARCHAR(MAX)') attributeName,
    x.y.value('.', 'VARCHAR(MAX)') attributeValue
FROM @GenScreenXML t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]/@*') x(y)

Try these and let us know how you get on.

Thursday, June 21, 2012 9:59 AM

0
Hi wBob,

thank you very much, these worked fine but the only problem is that, when I replace the table with actual one I got this messge:

When I first executed the code I got this message with:

Incorrect syntax near the keyword 'AS'

"(1 row(s) affected)

(2 row(s) affected)

Msg 4121, Level 16, State 1, Line 209
Cannot find either column "x" or the user-defined function or aggregate "x.y.value", or the name is ambiguous. I then did this:
```
SELECT 
	x.y.query('.'),
	x.y.value('@TABLE', 'VARCHAR(MAX)') [table],
	x.y.value('@COLS', 'VARCHAR(MAX)') cols,
	x.y.value('@CONDITIONS', 'VARCHAR(MAX)') conditions
FROM GenScreenXML t
	CROSS APPLY  t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]') x(y)
```
Incorrect syntax near the keyword 'AS'
And I then got this "The XMLDT method 'nodes' can only be invoked on columns of type xml." So i did this
```
SELECT 
	x.y.query('.'),
	x.y.value('@TABLE', 'VARCHAR(MAX)') [table],
	x.y.value('@COLS', 'VARCHAR(MAX)') cols,
	x.y.value('@CONDITIONS', 'VARCHAR(MAX)') conditions
FROM GenScreenXML t
	CROSS APPLY  CAST(t.ScreenXML AS XML).nodes('//SELECT[contains(@TABLE,"UserMaster")]') x(y)
```
Now, I get syntax error near the keyword 'AS', What am i doing wrong? AKu
Aku
- Reply
- Quote

Thursday, June 21, 2012 10:01 AM

Answerer

Your source column must be VARCHAR or NVARCHAR datatype instead of XML. You must cast it first, eg

SELECT 
	x.y.query('.'),
	x.y.value('@TABLE', 'VARCHAR(MAX)') [table],
	x.y.value('@COLS', 'VARCHAR(MAX)') cols,
	x.y.value('@CONDITIONS', 'VARCHAR(MAX)') conditions
FROM ( SELECT CAST( ScreenXML AS XML ) ScreenXML FROM @GenScreenXML ) t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]') x(y)


SELECT 
	x.y.value('local-name(.)', 'VARCHAR(MAX)') attributeName,
    x.y.value('.', 'VARCHAR(MAX)') attributeValue
FROM ( SELECT CAST( ScreenXML AS XML ) ScreenXML FROM @GenScreenXML ) t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]/@*') x(y)

Proposed As Answer by wBobMicrosoft Community Contributor, Editor Thursday, June 21, 2012 10:44 AM
Marked As Answer by AkuYali Thursday, June 21, 2012 3:31 PM

Thursday, June 21, 2012 3:46 PM

0

Hi wBob,

The above code worked very well but I get these message below, i identifies a couple of records but when I them as a single record, they seem fine. There are 390 records in my table and the first code generates 4194 rows and then breaks with the message. The second code generates 26443 rows and then breaks with the same message. Any idea of the anything else I can try? thank you, Aku

"Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding"
Aku
- Reply
- Quote

Thursday, June 21, 2012 5:39 PM

Answerer

You must have some XML with a header. Remove the header when converting to XML, eg:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- Clean the XML
SELECT CAST( REPLACE( ScreenXML, '<?xml version="1.0" encoding="utf-8"?>', '' ) AS XML ) ScreenXML
INTO #tmp
FROM @GenScreenXML 

SELECT 
	x.y.query('.'),
	x.y.value('@TABLE', 'VARCHAR(MAX)') [table],
	x.y.value('@COLS', 'VARCHAR(MAX)') cols,
	x.y.value('@CONDITIONS', 'VARCHAR(MAX)') conditions
FROM #tmp t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]') x(y)


SELECT 
	x.y.value('local-name(.)', 'VARCHAR(MAX)') attributeName,
    x.y.value('.', 'VARCHAR(MAX)') attributeValue
FROM #tmp t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]/@*') x(y)

Friday, June 22, 2012 9:26 AM

0
I really thought this was it but when I executed
```
SELECT CAST( REPLACE( ScreenXML, '<?xml version="1.0" encoding="utf-8"?>', '' ) AS XML ) ScreenXML
INTO #tmp
FROM @GenScreenXML 
```
I got this "Msg 8116, Level 16, State 1, Line 1

Argument data type ntext is invalid for argument 1 of replace function." so I tried this
```
SELECT Code, REPLACE( CAST(ScreenXML AS XML),'<?xml version="1.0" encoding="utf-8"?>', '' ) ScreenXML
INTO #TempGenScreenXML 
FROM GenScreenXML 
```
Then i got "
Msg 8116, Level 16, State 1, Line 1

Argument data type xml is invalid for argument 1 of replace function."

I'll now try the replace function on it own...

Aku
- Reply
- Quote

Friday, June 22, 2012 10:39 AM

Answerer

OK, nearly there then. Please check your data for XML headers to see if they are the same as the ones we are replacing ( ie the bit which cotains <?xml version ... > ).

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp ( ScreenXML XML )

-- Clean the XML
INSERT INTO #tmp
SELECT REPLACE( CAST( ScreenXML AS NVARCHAR(MAX) ), '<?xml version="1.0" encoding="utf-8"?>', '' )
FROM @GenScreenXML 

SELECT 
	x.y.query('.'),
	x.y.value('@TABLE', 'VARCHAR(MAX)') [table],
	x.y.value('@COLS', 'VARCHAR(MAX)') cols,
	x.y.value('@CONDITIONS', 'VARCHAR(MAX)') conditions
FROM #tmp t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]') x(y)


SELECT 
	x.y.value('local-name(.)', 'VARCHAR(MAX)') attributeName,
    x.y.value('.', 'VARCHAR(MAX)') attributeValue
FROM #tmp t
	CROSS APPLY t.ScreenXML.nodes('//SELECT[contains(@TABLE,"UserMaster")]/@*') x(y)

Friday, June 22, 2012 3:24 PM

0

Hi wBob,

Your are right, one record has that header tag so I filtered it out for the meantime and the rest of the code is fine.

thank you

Aku

Aku
- Reply
- Quote