dastaub
asked on
SQL XML
The below SQL query successfully returns a single column and row with XML. How can I assign a column name to the column that is returned?
select FacNo,VisitID, HospAcct, MRN from Visit for XML path ('Visits'), ROOT ('Patients')
Inside the single column and row is the below XML. I need to assign a column name to the column in order to make reference to it in code.
<Patients>
<Visits>
<FacNo>3</FacNo>
<VisitID>156</VisitID>
<HospAcct>007525009</HospA cct>
<MRN>163988</MRN>
</Visits>
<Visits>
<FacNo>3</FacNo>
<VisitID>158</VisitID>
<HospAcct>007525041</HospA cct>
<MRN>036475</MRN>
</Visits>
<Visits>
<FacNo>3</FacNo>
<VisitID>160</VisitID>
<HospAcct>007525108</HospA cct>
<MRN>166346</MRN>
</Visits>
</Patients>
select FacNo,VisitID, HospAcct, MRN from Visit for XML path ('Visits'), ROOT ('Patients')
Inside the single column and row is the below XML. I need to assign a column name to the column in order to make reference to it in code.
<Patients>
<Visits>
<FacNo>3</FacNo>
<VisitID>156</VisitID>
<HospAcct>007525009</HospA
<MRN>163988</MRN>
</Visits>
<Visits>
<FacNo>3</FacNo>
<VisitID>158</VisitID>
<HospAcct>007525041</HospA
<MRN>036475</MRN>
</Visits>
<Visits>
<FacNo>3</FacNo>
<VisitID>160</VisitID>
<HospAcct>007525108</HospA
<MRN>166346</MRN>
</Visits>
</Patients>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
your example does work, but if I replace the temp table with the actual table, it returns an empty column. It does not create an error only an empty column.
can you give us the SQL Statement that you used?
I think you just need the TYPE keyword.
SELECT FacNo,VisitID, HospAcct, MRN
FROM Visit
FOR XML PATH('Visits'), ROOT('Patients'), TYPE
;
P.S. when you say you need to refer to it in code, are you using .NET?
If yes, you can read the XML directly into an XMLDocument, so you do not need to name the column. Further, you can use the column index as you know there is only one XML string in result.
If yes, you can read the XML directly into an XMLDocument, so you do not need to name the column. Further, you can use the column index as you know there is only one XML string in result.
ASKER
Yes, VB.NET
Can you post an example of what you just described in your P.S. above or send me to a link?
Can you post an example of what you just described in your P.S. above or send me to a link?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You. Many times sophisticated code gets in the way of explaining the concept.
ASKER
When I Execute:
SELECT FacNo,VisitID, HospAcct, MRN FROM Visit FOR XML PATH('Visits'), ROOT('Patients'), TYPE it return an empty column
When I Execute:
SELECT FacNo,VisitID, HospAcct, MRN FROM Visit FOR XML PATH('Visits'), ROOT('Patients')
it returns the XML with a system generated column name.
SELECT FacNo,VisitID, HospAcct, MRN FROM Visit FOR XML PATH('Visits'), ROOT('Patients'), TYPE it return an empty column
When I Execute:
SELECT FacNo,VisitID, HospAcct, MRN FROM Visit FOR XML PATH('Visits'), ROOT('Patients')
it returns the XML with a system generated column name.
"Empty column" - are you talking about the column name? If so, I think it does remove that because it knows it is XML type in SSMS. Does the VB.NET code display the XML at the console? It did for me, so just curious.
ASKER
Empty column = blank where data would be, not the column name. All the results are in SQL not in VB.NET.
the Column name is XML_F52E2B61-18A1-11dAb105 -008005F49 916B without TYPE
and (no column name) with TYPE
with XML_F52E2B61-18A1-11dAb105 -008005F49 916B the field contains XML data.
with (no column name) the field is blank
the Column name is XML_F52E2B61-18A1-11dAb105
and (no column name) with TYPE
with XML_F52E2B61-18A1-11dAb105
with (no column name) the field is blank
Hmm. That is strange. Is there something under the message tab?
Anyway, if the goal is to get this in VB.NET, you do not need to add TYPE. The TYPE suggestion was for when you were using this as a sub-select and wanted to retain the XML data type. For VB.NET, you can use the XmlReader approach I showed to get the information out without doing a derived query.
Anyway, if the goal is to get this in VB.NET, you do not need to add TYPE. The TYPE suggestion was for when you were using this as a sub-select and wanted to retain the XML data type. For VB.NET, you can use the XmlReader approach I showed to get the information out without doing a derived query.
ASKER
select * from (select FacNo,VisitID, HospAcct, MRN from Visit for XML path ('Visits'), ROOT ('Patients') ) y(ColumnName)
does give a column name of "ColumnName" except SQL no longer sees the data as XML but as Text.