How Read data from XML variable in Sqlserver and show in table format?

Question

I have below format of xml.

enter image description here

need to show this data below table format.

Table Format

http://msdn.microsoft.com/pl-pl/library/ms175160.aspx , http://stackoverflow.com/questions/9997520/using-openxml-in-sql-server-2008-stored-proc-insert-order-differs-from-xml-doc, http://www.informit.com/articles/article.aspx?p=26499 — Emilio Gort, Sep 30 '13 at 11:01
I have changes in the XML now it's validate properly, sorry in emergency I have added wrong XML. — KuldipMCA, Sep 30 '13 at 11:41

score 8 · Accepted Answer · answered Sep 30 '13 at 11:13

8

Once you've made your XML valid, use nodes

select 
    t.x.value('FacilityID[1]','int') as FacilityID,
    t.x.value('Category[1]','int') as CategryID
from @yourxml.nodes('/Facilities/Facility') t(x)

answered Sep 30 '13 at 11:13

podiluska

50,144
7
94
100

How Read data from XML variable in Sqlserver and show in table format?

1 Answers1