Parsing XML File into SQL Server database returns 0 rows

Question

I am an XML newb and so this relatively small project is causing me grief. I have the XML into a temp table data type XML and that works just fine.

I have found an example of how to then parse the xml into rows and columns however when I have set up a test of just 2 columns - I get 0 results when I am expecting 2 rows.

Can anyone assist please?

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = xmldata FROM tempXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT TransactionType, TransactionDate
FROM OPENXML(@hDoc, 'Handoff/ImmediateDetail')
WITH 
(
    TransactionType [VarChar](50) '@TransactionType',
    TransactionDate datetime '@TransactionDate'
)

EXEC sp_xml_removedocument @hDoc
GO

Sample XML:

<my:Handoff> 
    <ImmediateDetail OrderItemRef="1032355" TransactionType="Issue" 
                     TransactionDate="2016-09-29T12:22:00></ImmediateDetail>
</my:Handoff>

Cheers

Dave

Can you give us an example of the xml you are attempting to parse? — Mack
– Mack, Commented Dec 20, 2016 at 14:05
<my:Handoff > <ImmediateDetail OrderItemRef="1032355" TransactionType="Issue" TransactionDate="2016-09-29T12:22:00> </ImmediateDetail> </my:Handoff> — Dave Edmonds
– Dave Edmonds, Commented Dec 20, 2016 at 14:15
I got undeclared prefix so I removed the "my:" and there was a missing quote at the end of the date - but then it ran just fine. <Handoff > <ImmediateDetail OrderItemRef="1032355" TransactionType="Issue" TransactionDate="2016-09-29T12:22:00"> </ImmediateDetail> </Handoff> — Mack
– Mack, Commented Dec 20, 2016 at 14:17
Namespaces help you avoid naming conflicts..if you don't plan on encountering conflicts you can omit them however it's easier to add them in upfront rather than to retrofit later. — Mack
– Mack, Commented Dec 20, 2016 at 14:23

marc_s · Accepted Answer · 2016-12-20 14:20:36Z

2

If your XML didn't have any XML namespace, then you could use this code to retrieve the data (and insert it into the SQL Server table):

DECLARE @XML XML = '<Handoff> 
                        <ImmediateDetail OrderItemRef="1032355" TransactionType="Issue" TransactionDate="2016-09-29T12:22:00" />
                    </Handoff>'

SELECT
    OrderItemRef = XC.value('@OrderItemRef', 'bigint'),
    TransactionType = XC.value('@TransactionType', 'varchar(50)'),
    TransactionDate = XC.value('@TransactionDate', 'datetime')
FROM
    @XML.nodes('/Handoff/ImmediateDetail') AS XT(XC)

If your XML document does have an XML namespace - then you need to use it and include it in your query:

WITH XMLNAMESPACES('.....' as my)
SELECT
    OrderItemRef = XC.value('@OrderItemRef', 'bigint'),
    TransactionType = XC.value('@TransactionType', 'varchar(50)'),
    TransactionDate = XC.value('@TransactionDate', 'datetime')
FROM
    @XML.nodes('/my:Handoff/my:ImmediateDetail') AS XT(XC)

answered Dec 20, 2016 at 14:20

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Dave Edmonds Over a year ago

Cheers - I was trying the pre 2005 route without using nodes - however maybe I need to go back to the drawing board. XT(XC) is confusing - what does this bit do? Dave

marc_s Over a year ago

@DaveEdmonds: the .nodes() function returns a "pseudo table" with one column of XML type - the XT stands as the table alias for that pseudo table, and the XC is the column name for that XML typed column in that pseudo table

marc_s Over a year ago

@DaveEdmonds: well yes - if you do have a definition for the XML namespace that uses the my: prefix - then you need to use that string in this place here - since you haven't shown that namespace definition, I cannot possibly know what that is, so therefore the ..... ...

Dave Edmonds Over a year ago

I know it - that's fine thank you for your help. Dave

marc_s Over a year ago

@DaveEdmonds: also please do not put code samples or sample data into comments - since you cannot format it, it's extremely hard to read it.... Instead: update your question by editing it to provide that additional information! Thank you.

|

Mack · Accepted Answer · 2016-12-20 14:30:19Z

0

I would say there is probably something wrong with the contents of the table tempXML.

I just ran this code:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = '<Handoff><ImmediateDetail OrderItemRef="1032355" TransactionType="Issue" TransactionDate="2016-09-29T12:22:00"> </ImmediateDetail> </Handoff>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT TransactionType,TransactionDate
FROM OPENXML(@hDoc, 'Handoff/ImmediateDetail')
WITH 
(
TransactionType [VarChar](50) '@TransactionType',
TransactionDate datetime '@TransactionDate'

)

EXEC sp_xml_removedocument @hDoc
GO

and I got this result

TransactionType TransactionDate
Issue           2013-06-21 13:50:00.000

which is what I would expect...

Any chance you could give us more details about tempXml or the data therein?

Here is some basic reading material on XML Namespaces

http://www.w3schools.com/xml/xml_namespaces.asp

https://msdn.microsoft.com/en-us/library/ms177607.aspx

edited Dec 20, 2016 at 14:30

answered Dec 20, 2016 at 14:13

Mack

2,5521 gold badge26 silver badges44 bronze badges

1 Comment

Dave Edmonds Over a year ago

Hi thanks for the reply. TempXml contains the xml I posted as a 1 line xml field that is imported from the XML file.

Dave Edmonds · Accepted Answer · 2016-12-20 14:46:55Z

0

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = xmldata FROM tempXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML;

WITH XMLNAMESPACES('http://www.example.com' as my)

SELECT
    OrderItemRef = XC.value('@OrderItemRef', 'bigint'),
    TransactionType = XC.value('@TransactionType', 'varchar(50)'),
    TransactionDate = XC.value('@TransactionDate', 'datetime')
FROM
    @XML.nodes('/my:Handoff/ImmediateDetail') AS XT(XC)


EXEC sp_xml_removedocument @hDoc
GO

Solved!!! Many thanks one and all!! Dave

answered Dec 20, 2016 at 14:46

Dave Edmonds

871 gold badge1 silver badge9 bronze badges

1 Comment

marc_s Over a year ago

Now that you're using the built-in XQuery support using .nodes(), you don't need those memory leaking sp_xml_preparedocument and sp_xml_removedocument calls anymore - just toss those out

Collectives™ on Stack Overflow

Parsing XML File into SQL Server database returns 0 rows

3 Answers 3

13 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related