sql - Parsing xml file and getting parent names of nodes using stored procedure -

sql - Parsing xml file and getting parent names of nodes using stored procedure -

i have xml file given input stored procedure in sql server. have table has columns element name , parent id. root element parent id 0 first element parent id 1and on. how accomplish this?

i have several complex types in xml

<voyageordermessage xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:nonamespaceschemalocation="voyage.xsd"> <messageheader> <messageid>id</messageid> <messagedate>2009-11-01t11:42:07.414+03:00</messagedate> <messagetypeversion>version</messagetypeversion> <senderid>si</senderid> <receiverdetails> <receivermethod /> <receiverformat /> <receiveraddress></receiveraddress> </receiverdetails> </messageheader> <voyageorder> <voyageid>rg-fuw-001</voyageid> <amendment>4</amendment> <imo>9256200</imo> <vesselname>fuwairit</vesselname> <shipmastername /> <orderdate>2009-11-01t11:41:59.149+03:00</orderdate> <passage> <passagenumber>1</passagenumber> <passagetype>laden</passagetype> <departureportname>ras laffan</departureportname> <departureportcode>rlf</departureportcode> <departuretime>2009-10-06t19:06:00.000+03:00</departuretime> <arrivalportname>suez</arrivalportname> <arrivalportcode>suz</arrivalportcode> <arrivaltime>2009-10-13t03:00:00.000+02:00</arrivaltime> </passage>

example output

elementname parent id column ------------------------------ voyageorder 0 1 messageheader 1 2 messageid 2 3 etc

any help appreciated

looks border table format returned openxml use.

sql fiddle

ms sql server 2012 schema setup:

query 1:

declare @xml xml = ' <voyageordermessage xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:nonamespaceschemalocation="voyage.xsd"> <messageheader> <messageid>id</messageid> <messagedate>2009-11-01t11:42:07.414+03:00</messagedate> </messageheader> <voyageorder> <voyageid>rg-fuw-001</voyageid> <amendment>4</amendment> <orderdate>2009-11-01t11:41:59.149+03:00</orderdate> <passage> <passagenumber>1</passagenumber> <passagetype>laden</passagetype> </passage> </voyageorder> </voyageordermessage>' declare @idoc int exec sp_xml_preparedocument @idoc output, @xml select * openxml(@idoc, '*') exec sp_xml_removedocument @idoc

results:

| id | parentid | nodetype | localname | prefix | namespaceuri | datatype | prev | text | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | (null) | 1 | voyageordermessage | (null) | (null) | (null) | (null) | (null) | | 2 | 0 | 2 | xsi | xmlns | (null) | (null) | (null) | (null) | | 14 | 2 | 3 | #text | (null) | (null) | (null) | (null) | http://www.w3.org/2001/xmlschema-instance | | 3 | 0 | 2 | nonamespaceschemalocation | xsi | http://www.w3.org/2001/xmlschema-instance | (null) | (null) | (null) | | 15 | 3 | 3 | #text | (null) | (null) | (null) | (null) | voyage.xsd | | 4 | 0 | 1 | messageheader | (null) | (null) | (null) | (null) | (null) | | 5 | 4 | 1 | messageid | (null) | (null) | (null) | (null) | (null) | | 16 | 5 | 3 | #text | (null) | (null) | (null) | (null) | id | | 6 | 4 | 1 | messagedate | (null) | (null) | (null) | 5 | (null) | | 17 | 6 | 3 | #text | (null) | (null) | (null) | (null) | 2009-11-01t11:42:07.414+03:00 | | 7 | 0 | 1 | voyageorder | (null) | (null) | (null) | 4 | (null) | | 8 | 7 | 1 | voyageid | (null) | (null) | (null) | (null) | (null) | | 18 | 8 | 3 | #text | (null) | (null) | (null) | (null) | rg-fuw-001 | | 9 | 7 | 1 | amendment | (null) | (null) | (null) | 8 | (null) | | 19 | 9 | 3 | #text | (null) | (null) | (null) | (null) | 4 | | 10 | 7 | 1 | orderdate | (null) | (null) | (null) | 9 | (null) | | 20 | 10 | 3 | #text | (null) | (null) | (null) | (null) | 2009-11-01t11:41:59.149+03:00 | | 11 | 7 | 1 | passage | (null) | (null) | (null) | 10 | (null) | | 12 | 11 | 1 | passagenumber | (null) | (null) | (null) | (null) | (null) | | 21 | 12 | 3 | #text | (null) | (null) | (null) | (null) | 1 | | 13 | 11 | 1 | passagetype | (null) | (null) | (null) | 12 | (null) | | 22 | 13 | 3 | #text | (null) | (null) | (null) | (null) | laden |

sql sql-server xml sql-server-2008