Openxml with Xmlns:Dt

OPENXML with xmlns:dt

Is there a particular reason that you need to use OPENXML to do this? You can easily get the information with a XQUERY in 2005 like this:

declare @xmldata xml    
set @xmldata = 
'<data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes">
  <products>
    <product>
      <product_id>121403</product_id>
      <countries>
        <dt:country>GBR</dt:country>
        <dt:country>USA</dt:country>
      </countries>
    </product>
  </products>
</data>'

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://www.aaa.com/master_browse_response',
    'http://www.aaa.com/DataTypes' as dt
)
SELECT  x.c.value('(../../product_id)[1]', 'varchar(100)') as product_id,
        x.c.value('(.)[1]', 'varchar(100)') as country
FROM @xmldata.nodes('/data/products/product/countries/dt:country') x(c)

The newer XQUERY capabilities are a much better choice for solving your problem.

EDIT:
The same solution with OPENXML would be:

declare @xmldata xml    
set @xmldata = 
'<data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes">
  <products>
    <product>
      <product_id>121403</product_id>
      <countries>
        <dt:country>GBR</dt:country>
        <dt:country>USA</dt:country>
      </countries>
    </product>
  </products>
</data>'

DECLARE @hDoc int, @rootxmlns varchar(100)
SET @rootxmlns = '<root xmlns:hm="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes"/>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns

SELECT *
FROM OPENXML(@hDoc, '//hm:product/hm:countries/dt:country',2)
        WITH(Country    varchar(100) '.',
             Product_ID varchar(100) '../../hm:product_id')

EXEC sp_xml_removedocument @hDoc

XML to sql with openxml

SELECT    *
FROM      OPENXML (@XmlDocumentHandle, '*[local-name()=''TrainingCenterDatabase'']/*[local-name()=''Activities'']/*[local-name()=''Activity'']')
           WITH (
                    Sport nvarchar(max) '@Sport'
                )

You may learn about XML namespaces.

In that particular case the expression xmlns="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2" in TrainingCenterDatabase node defines default namespace, so all descendant elements bounds to this namespace unless specified otherwise.

Your XPath is attempting to address elements that are bound to the default "no namespace" namespace, so they don't match.

You can execute

SELECT * FROM OPENXML (@XmlDocumentHandle, '.')

to see namespace of every element in 'namespaceuri' column of result table.

How to use OPENXML to load XML data into existing SQL Table?

Try this:

EXEC @Status = sp_xml_preparedocument @idoc OUTPUT, 
@xmlDocument, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
       xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
       xmlns:rs="urn:schemas-microsoft-com:rowset" 
       xmlns:z="#RowsetSchema"/>' 
SELECT 'sp_xml_preparedocument status=',@Status 

SELECT * 
FROM OPENXML (@idoc, '/xml/rs:data/z:row',1) 
WITH ( 
   HOMEROOM_TEACHER   INT    '@c0' 
  ,HOMEROOM_NUMBER    INT    '@c1' 
  ,ENTITY_ID          INT    '@c2' 
) 

I did a few things:

1. Added the namespace declaration as
   the third parameter to
   sp_xml_preparedocument.
2. Changed the xpath section from '/xml/' to '/xml/rs:data/z:row' to specify the correct position and namespaces in the XML document
3. Changed the @C variables to lower case (@c)

Results were:

HOMEROOM_TEACHER HOMEROOM_NUMBER ENTITY_ID
---------------- --------------- -----------
22943            101             55
22929            102             55
22854            103             55
22908            104             55
22881            105             55

FYI, information about using OPENXML with namespaces can be found here.

OPENXML with an embedded XSD using SQL Server 2012

You must declare the namespace using WITH XMLNAMESPACES and then prefix the names with the schema prefix (ns1 in my example) when query them. So your code should become something like this:

DECLARE @fileData XML

SELECT @fileData = BulkColumn 
FROM OpenRowSet(BULK 'E:\inbox\ogrid.xml',Single_blob) x;

WITH XMLNAMESPACES ('urn:schemas-microsoft-com:sql:SqlRowSet1' as ns1)  
SELECT 
    xdata.value('ns1:ogrid_cde[1]','int') ogrid_cde,
    xData.value('ns1:ogrid_nam[1]','nvarchar(255)') ogrid_name,
    xData.value('ns1:ogrid_adr_nam[1]','nvarchar(255)') ogrid_adr_name,
    xData.value('ns1:mail_stop[1]','nvarchar(255)') mail_stop,
    xData.value('ns1:line1_adr[1]','nvarchar(255)') line1_adr,
    xData.value('ns1:line2_adr[1]','nvarchar(255)') line2_adr,
    xData.value('ns1:line3_adr[1]','nvarchar(255)') line3_adr,
    xData.value('ns1:city_nam[1]','nvarchar(255)') city_name,
    xData.value('ns1:st_nam[1]','nvarchar(255)') st_name,
    xData.value('ns1:zip_cde[1]','nvarchar(255)') zip_cde,
    xData.value('ns1:ctry_nam[1]','nvarchar(255)') ctry_name,
    xData.value('ns1:phone_num[1]','decimal(28,10)') phone_num,
    xData.value('ns1:fax_num[1]','decimal(28,10)') fax_num,
    xData.value('ns1:stat_eff_dte[1]','datetime') stat_eff_dte,
    xData.value('ns1:issng_ag_cde[1]','nvarchar(255)') issng_ag_cde,
    xData.value('ns1:lst_modified_dte[1]','datetime') last_modified_dte,
    xData.value('ns1:created_dte[1]','datetime') created_dte,
    xData.value('ns1:ogrid_stat_cde[1]','nvarchar(255)') ogrid_stat_cde
FROM 
    @fileData.nodes('root/ns1:ogrid') AS x(xData)

Also, decimal(28,10) type doesn't make much sense for phone and fax numbers.

And more important, tell your supervisor that you published all the data - names, addresses, phone numbers, etc. Your company may need to know about that. Next time when you want to do this, modify the XML to leave only few rows in it and anonymize the data.

OPENXML not returning data, no error message

Starting from the code from the link provided in the comments you can use this code:

DECLARE @X XML

SELECT @X=MC2016
FROM OPENROWSET (BULK 'c:\TEMP\Detail_2016.xml', SINGLE_BLOB) AS MEDPOP_2016(MC2016)

DECLARE @hdoc int

EXEC SP_XML_PREPAREDOCUMENT @hdoc OUTPUT, @X, '<myNs xmlns:ns1="http://www.dxcg.com/Results.xsd"/>'

;WITH XMLNAMESPACES ('http://www.dxcg.com/Results.xsd' as ns1) 
select * 
from openxml (@hdoc, '/ns1:Results/ns1:Record', 1)
with (
    MemberID varchar(10)
    )

Results:

Note: the xml you posted is probably missing a the closing tag for root element </Results>

From Excel to DataTable in C# with Open XML

I think this should do what you're asking. The other function is there just to deal with if you have shared strings, which I assume you do in your column headers. Not sure this is perfect, but I hope it helps.

static void Main(string[] args)
{
    DataTable dt = new DataTable();

    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"..\..\example.xlsx", false))
    {

        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();

        foreach (Cell cell in rows.ElementAt(0))
        {
            dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }

        foreach (Row row in rows) //this will also include your header row...
        {
            DataRow tempRow = dt.NewRow();

            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));
            }

            dt.Rows.Add(tempRow);
        }

    }
    dt.Rows.RemoveAt(0); //...so i'm taking it out here.

}


public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = cell.CellValue.InnerXml;

    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
        return value;
    }
}

Replace token inside word table using open xml using c#

I am able to resolved this issue using DocumentAssembler (http://www.ericwhite.com/blog/documentassembler-developer-center/) in open-xml-powertools, which is suggested by Eric White.

DocumentAssembler is providing verity of option to process document for repeating contents, tables, Match conditions.

---

Related Topics